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.

 All Forums
 Site Related Forums
 Article Discussion
 Article: Working with Time Spans and Durations in SQL Server

Author  Topic 

Ask SQLTeam Question

0 Posts

Posted - 2007-10-15 : 08:36:08
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 - 2007-10-15 : 08:41:33
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 - 2008-01-04 : 15:23:48
This was extremely helpful. Thank you!

Rye Guy
Go to Top of Page

Starting Member

2 Posts

Posted - 2008-04-29 : 14:19:20
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 - 2008-04-29 : 15:20:05
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 Post

Posted - 2008-08-01 : 19:34:53
I just post another implemantation of time interval using BIGINT at
Go to Top of Page

Starting Member

2 Posts

Posted - 2009-02-07 : 03:30:59
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 Post

Posted - 2009-11-24 : 06:22:42
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

Master Smack Fu Yak Hacker

2916 Posts

Posted - 2009-11-24 : 06:56:44
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.
Go to Top of Page

Starting Member

5 Posts

Posted - 2011-08-16 : 20:12:37
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
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

- Advertisement -