Article: Working with Time Spans and Durations in SQL Server

Please start any new threads on our new site at https://forums.sqlteam.com. 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

AskSQLTeam
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

SwePeso
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 http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=77202

E 12°55'05.25"
N 56°04'39.16"

Rye Guy
Starting Member

3 Posts

Posted - 2008-01-04 : 15:23:48

This was extremely helpful. Thank you!

Rye Guy

jetcity73
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. --

jsmith8858
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
http://weblogs.sqlteam.com/JeffS

qingsongyao
Starting Member

1 Post

Posted - 2008-08-01 : 19:34:53

I just post another implemantation of time interval using BIGINT at blogs.msdn.com/qingsongyao

sbhavan_s
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.

And,
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

Dehaut
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)
RETURNS CHAR(8)
WITH SCHEMABINDING
AS
BEGIN
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))
END
END
GO
SELECT b.Batch_Log_ID,
CONVERT(nvarchar(23),b.Log_Open_DT,120) as StartTime,
CONVERT(nvarchar(23),d.DateTime,120) as EndTime,
FnSecToTime(DateDiff(s,b.Log_Open_DT,d.DateTime))
FROM BatchIDLog b
JOIN MaterialInput m
ON b.Batch_Log_ID = m.Batch_Log_ID
LEFT OUTER JOIN BatchDetail d
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.

AndrewMurphy
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.

toddmo
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,
	@EndDate DATETIME
)
RETURNS		@Duration
TABLE (		Hours INT,
			Minutes INT,
			Seconds INT
)
AS BEGIN
	/*	EXAMPLE USAGE:
		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
	RETURN
END

CREATE FUNCTION dbo.[DateTime.Duration.ToString]
(
	@StartDate DATETIME,
	@EndDate DATETIME,
	@Format INT = 0 -- 0 = short (00:00:00), 1 = long (00 Hours, 00 Minutes, 00 Seconds)
)
RETURNS VARCHAR(MAX)
AS BEGIN
	/*	EXAMPLE USAGE:
		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)
	*/
	DECLARE		@ToString VARCHAR(MAX)
	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)
				END
	FROM		dbo.[DateTime.Duration](@StartDate,@EndDate)
	RETURN		@ToString
END

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources