Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 Site Related Forums
 Article Discussion
 Article: Working with Time Spans and Durations in SQL Server
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Ask SQLTeam Question

0 Posts

Posted - 10/15/2007 :  08:36:08  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
What is the best way to return the "duration" of an event in SQL, given the start and end datetime values? How can we add up these durations to return grand totals? What data types should be used to return this data to our clients? How do we handle overflows, such as when hours go over 23 or minutes total up to over 59? Are there any T-SQL functions or other techniques that are useful in these scenarios?

Read Working with Time Spans and Durations in SQL Server

Patron Saint of Lost Yaks

30421 Posts

Posted - 10/15/2007 :  08:41:33  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Nice and well described article!
Thanks for that Jeff.

Here is another similar approach

E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

Rye Guy
Starting Member

3 Posts

Posted - 01/04/2008 :  15:23:48  Show Profile  Reply with Quote
This was extremely helpful. Thank you!

Rye Guy
Go to Top of Page

Starting Member

2 Posts

Posted - 04/29/2008 :  14:19:20  Show Profile  Reply with Quote
Jeff, about your final note below, what if the exact StartDate and EndDate are provided, then can you provide duration in Months and Days? (Notice one is leap year and the other is not.)
Start Date | End Date | months & days | day count
02/01/2008 | 03/15/2008 | 1 & 14 | 43
02/01/2009 | 03/15/2009 | 1 & 14 | 42

Excel does this using the "datediF" function.

--On a final note, what if we wish to break down our Durations or TimeSpans into Months or Years? The answer is that you cannot -- those units are not precise and vary from year to year and month to month, so the largest unit you can calculate with a TimeSpan is Days. After all, how many months is 29 days? It could be one, it could be zero. And you could break 35 days down into 1 month/4 days (for months with 31 days), or 1 month/5 days (30 day months), and so on. Even years vary between 365 and 366 days. You can always estimate or round TimeSpans to these units (i.e., 360 days is approximately 12 months, or 731 days is approximately 2 years) but you cannot return and calculate precise results in those units. --
Go to Top of Page

Dr. Cross Join

7423 Posts

Posted - 04/29/2008 :  15:20:05  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
jetcity73 -- You would use DateDiff(), similar to Excel, to find the number of months "crossed" between two dates.

Note that, like Excel, the results may not always be what you are looking for. For example, consider these two date ranges:

1/1/2008 and 2/28/2008 (59 days)
1/31/2008 and 2/1/2008 (1 day)

Even though one is 59 days and the other is 1 day, they both have a difference of 1 month.

- Jeff
Go to Top of Page

Starting Member

1 Posts

Posted - 08/01/2008 :  19:34:53  Show Profile  Visit qingsongyao's Homepage  Reply with Quote
I just post another implemantation of time interval using BIGINT at
Go to Top of Page

Starting Member

2 Posts

Posted - 02/07/2009 :  03:30:59  Show Profile  Reply with Quote
Hi Jeff,
It's a really fantastic explanation. Thanks for that.

I get the wrong result when I used the following.
SELECT empname, sum(datediff(second, starttime, endtime)/3600) as hours, (sum(datediff(second, starttime, endtime)%3600)/60) as
minutes, sum(datediff(second, starttime, endtime) %60) AS seconds FROM Time group by empname

Dev - 1 - 45 - 79
Jaya - 26 - 0 - 62

Please let me know where the error is.

Calculating the SUM() of DateTime Values

In your example you have given day, hour, minute and seconds, but what I want is "total hours" not date, like the below one.

Your example
1 day 17 hours 36 minutes 13 seconds
But i would like to get, the 1 day ie 24 hours should be added with 17 hours like
41 hours 36 miuntes 13 seconds or 41:36:13

Thanks in advance

Go to Top of Page

Starting Member

1 Posts

Posted - 11/24/2009 :  06:22:42  Show Profile  Reply with Quote
Hello everybody,
I'm a young developper in SQL and I work with SQL Server for reporting. I want to show in my tab 4 colons(batch,startime,endtime and duration).
This my script

CREATE FUNCTION FnSecToTime(@_secondes INT)
RETURN CASE LEN(@_secondes / 3600)
WHEN 1 THEN '0' + CAST (@_secondes / 3600 AS CHAR(1))
ELSE CAST (@_secondes / 3600 AS CHAR(2))
END +':'+
CASE LEN(@_secondes / 60) % 60
WHEN 1 THEN '0' + CAST ((@_secondes / 60) % 60 AS CHAR(1))
ELSE CAST ((@_secondes / 60) % 60 AS CHAR(2))
END +':'+
CASE LEN(@_secondes % 60)
WHEN 1 THEN '0' + CAST (@_secondes % 60 AS CHAR(1))
ELSE CAST (@_secondes % 60 AS CHAR(2))
SELECT b.Batch_Log_ID,
CONVERT(nvarchar(23),b.Log_Open_DT,120) as StartTime,
CONVERT(nvarchar(23),d.DateTime,120) as EndTime,
FROM BatchIDLog b
JOIN MaterialInput m
ON b.Batch_Log_ID = m.Batch_Log_ID
ON b.Batch_Log_ID = d.Batch_Log_ID
AND d.DateTime = (SELECT MAX(DateTime)
FROM BatchDetail
WHERE Batch_Log_ID = d.Batch_Log_ID
GROUP BY d.Batch_Log_ID,b.Campaign_ID,b.Lot_ID,b.Batch_ID, b.Recipe_ID,d.DateTime)
WHERE b.Campaign_ID LIKE '#Campaign_ID#'
AND b.Lot_ID LIKE '#Lot_ID#'
AND b.Batch_ID LIKE '#Batch_ID#'
AND b.Batch_Log_ID LIKE '#Batch_Log_ID#'
AND b.Product_ID LIKE '#Product_ID#'
AND b.Recipe_ID LIKE '#Recipe_ID#'
AND b.Recipe_Version LIKE '#Version#'
AND b.Train_ID LIKE '#Train_ID#'
AND ( b.Campaign_ID + '/' + b.Lot_ID + '/' + b.Batch_ID ) LIKE '#CLB#'
GROUP BY b.Batch_Log_ID,b.Campaign_ID,b.Lot_ID,b.Batch_ID,b.Recipe_ID,b.Log_Open_DT,d.DateTime

But I've this message error : Incorrect syntax near the keyword 'SELECT'. I don't find the error if someone has good eyes than me, i'll be happy to learn his instruction.
Go to Top of Page

Flowing Fount of Yak Knowledge

2916 Posts

Posted - 11/24/2009 :  06:56:44  Show Profile  Reply with Quote
You have a "GO" command in the middle of the script.

And...FYI. Best Practice is to start a new post, rather than re-activate somebody else's.

Edited by - AndrewMurphy on 11/24/2009 09:13:01
Go to Top of Page

Starting Member

5 Posts

Posted - 08/16/2011 :  20:12:37  Show Profile  Reply with Quote
I came up with these two functions: one to calculate and the other to display. Generic and ready to re-use:

CREATE FUNCTION dbo.[DateTime.Duration]
	@StartDate DATETIME,
RETURNS		@Duration
TABLE (		Hours INT,
			Minutes INT,
			Seconds INT
		SELECT * FROM dbo.[DateTime.Duration]('3/15/2009 3:30 AM','3/16/2009 4:45 PM')
	INSERT		@Duration
	SELECT		TotalSeconds / 3600 as Hours, 
				(TotalSeconds % 3600) / 60 as Minutes, 
				TotalSeconds % 60 as Seconds
	FROM		(SELECT DateDiff(second, @StartDate, @EndDate) as TotalSeconds) d

CREATE FUNCTION dbo.[DateTime.Duration.ToString]
	@StartDate DATETIME,
	@Format INT = 0 -- 0 = short (00:00:00), 1 = long (00 Hours, 00 Minutes, 00 Seconds)
		SELECT dbo.[DateTime.Duration.ToString]('3/15/2009 3:30 AM','3/16/2009 4:45 PM',0)
		SELECT dbo.[DateTime.Duration.ToString]('3/15/2009 3:30 AM','3/16/2009 4:45 PM',1)
	SELECT		@ToString
	=			CASE	@Format
				WHEN	1
				THEN	RIGHT('0' + CONVERT(VARCHAR,Hours),2) + ' Hours, ' + RIGHT('0' + CONVERT(VARCHAR,Minutes),2) + ' Minutes, ' + RIGHT('0' + CONVERT(VARCHAR,Seconds),2) + ' Seconds'
				ELSE	RIGHT('0' + CONVERT(VARCHAR,Hours),2) + ':' + RIGHT('0' + CONVERT(VARCHAR,Minutes),2) + ':' + RIGHT('0' + CONVERT(VARCHAR,Seconds),2)
	FROM		dbo.[DateTime.Duration](@StartDate,@EndDate)
	RETURN		@ToString

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000