SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

AskSQLTeam
Ask SQLTeam Question

USA
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

SwePeso
Patron Saint of Lost Yaks

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



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

jetcity73
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

jsmith8858
Dr. Cross Join

USA
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
http://weblogs.sqlteam.com/JeffS
Go to Top of Page

qingsongyao
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 blogs.msdn.com/qingsongyao
Go to Top of Page

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

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




Go to Top of Page

Dehaut
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)
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.
Go to Top of Page

AndrewMurphy
Flowing Fount of Yak Knowledge

Ireland
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

toddmo
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,
	@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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.17 seconds. Powered By: Snitz Forums 2000