DateDiff - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

DateDiff

Author

Topic

belkin_99
Starting Member

27 Posts

Posted - 2008-09-23 : 00:14:47

When I applied
Select Datediff (hh, '09/22/2008 00:35:3','09/22/2008 01:05:45')AS HourInterval
Select Datediff (mi, '09/22/2008 00:35:3','09/22/2008 01:05:45')AS MinuteInterval
Select Datediff (ss, '09/22/2008 00:35:3','09/22/2008 01:05:45')AS SecondInterval

The Results are:

HourInterval=1
MinuteInterval=30
SecondInterval = 1842

While the real interval time Should be:
HourInterval=0
MinuteInterval=30
SecondInterval = 42

Can Some one Explain the different in the two results, Please?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-23 : 00:27:31

datediff wont compare the actual time difference between two time values. what it does is to take hour/min/sec part of each time and then give difference. so first case hour parts are 0 and 1 and difference is 1 and so on. to get actual hour/sec/min diff do like below

Select Datediff (ss, '09/22/2008 00:35:3','09/22/2008 01:05:45')*1.0/(60 * 60)AS HourInterval,
 (Datediff (ss, '09/22/2008 00:35:3','09/22/2008 01:05:45')*1.0%(60 * 60))/60AS MinuteInterval,
 ((Datediff (ss, '09/22/2008 00:35:3','09/22/2008 01:05:45')*1.0%(60 * 60))%60)/60)AS SecondInterval

Windza
Yak Posting Veteran

61 Posts

Posted - 2008-09-23 : 00:33:54

While I'm definitely no expert - could it have something to do with DATEDIFF only counting boundaries crossed as opposed to the actual interval ? i.e. 0 to 1 crosses 1 boundary even though a full hour has not passed...
Just my 2CW anyhow...

Sorry - late post - I think we're saying the same thing though :-)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-23 : 07:21:39

See http://www.sqlteam.com/article/datediff-function-demystified for explanation.

E 12°55'05.63"
N 56°04'39.26"

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-09-23 : 12:14:02

Depending on that you are trying to accomplish (other than understanding the nuances of DATEDIFF) you might try something like:

SELECT ABS(DATEPART(HOUR, '09/22/2008 01:05:45') - DATEPART(HOUR, '09/22/2008 00:35:3'))
SELECT ABS(DATEPART(MINUTE, '09/22/2008 00:35:3') - DATEPART(MINUTE, '09/22/2008 01:05:45'))
SELECT ABS(DATEPART(SECOND, '09/22/2008 01:05:45') - DATEPART(SECOND, '09/22/2008 00:35:3'))

belkin_99
Starting Member

27 Posts

Posted - 2008-09-23 : 19:28:35

Mr. visakh16
you are the man.
this is work perfectly,

Thanks,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-24 : 01:21:47

quote:
Originally posted by belkin_99

Mr. visakh16
you are the man.
this is work perfectly,

Thanks,

welcome

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-09-24 : 07:32:13

quote:
Originally posted by Lamprey

Depending on that you are trying to accomplish (other than understanding the nuances of DATEDIFF) you might try something like:
SELECT ABS(DATEPART(HOUR, '09/22/2008 01:05:45') - DATEPART(HOUR, '09/22/2008 00:35:3'))
SELECT ABS(DATEPART(MINUTE, '09/22/2008 00:35:3') - DATEPART(MINUTE, '09/22/2008 01:05:45'))
SELECT ABS(DATEPART(SECOND, '09/22/2008 01:05:45') - DATEPART(SECOND, '09/22/2008 00:35:3'))

This will not work in many cases. See http://www.sqlteam.com/article/working-with-time-spans-and-durations-in-sql-server under the "converting time units with math" section.

- Jeff
http://weblogs.sqlteam.com/JeffS

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-09-24 : 11:27:52

Here is a simple way to handle elapsed time differences.


select
	[Days]		= datediff(day,0,ET-ST),
	[Hours]		= datepart(Hour,ET-ST),
	[Minutes]	= datepart(Minute,ET-ST),
	[Seconds]	= datepart(Second,ET-ST),
	[Milliseconds]	= datepart(millisecond,ET-ST)
from
	(
	select	-- Test Data
		ST = convert(datetime,'2008/09/22 00:35:33.997'),
		ET = convert(datetime,'2009/10/22 01:05:45.443')
	) a

Results:
Days        Hours       Minutes     Seconds     Milliseconds 
----------- ----------- ----------- ----------- ------------ 
395         0           30          11          447

(1 row(s) affected)

CODO ERGO SUM

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-09-24 : 11:56:06

quote:
Originally posted by jsmith8858

quote:
Originally posted by Lamprey

Depending on that you are trying to accomplish (other than understanding the nuances of DATEDIFF) you might try something like:
SELECT ABS(DATEPART(HOUR, '09/22/2008 01:05:45') - DATEPART(HOUR, '09/22/2008 00:35:3'))
SELECT ABS(DATEPART(MINUTE, '09/22/2008 00:35:3') - DATEPART(MINUTE, '09/22/2008 01:05:45'))
SELECT ABS(DATEPART(SECOND, '09/22/2008 01:05:45') - DATEPART(SECOND, '09/22/2008 00:35:3'))

This will not work in many cases. See http://www.sqlteam.com/article/working-with-time-spans-and-durations-in-sql-server under the "converting time units with math" section.

- Jeff
http://weblogs.sqlteam.com/JeffS

EDIT: I reread some of the post and I see that I was approaching this from a different angle. I was just looking at the difference in a particular time segment (i.e. the HOUR or MINUTE difference). I see that if you are trying to get the exact duration between two date times, then what I was demonstrating is not applicable. So, please disregaurd my post... :)

So, please ignore from here down..

Actually it does work. The difference in what I showed is that I get the DATEPART of each date first then do the math versus using DATEDIFF then getting the DATE part.

-- MJV
select
	[Days]		= datediff(day,0,ET-ST),
	[Hours]		= datepart(Hour,ET-ST),
	[Minutes]	= datepart(Minute,ET-ST),
	[Seconds]	= datepart(Second,ET-ST),
	[Milliseconds]	= datepart(millisecond,ET-ST)
from
	(
	select	-- Test Data
		ST = convert(datetime,'2008/09/22 00:35:33.997'),
		ET = convert(datetime,'2009/10/22 01:05:45.443')
	) a


--Lamprey
select
	[Days]		= ABS(DATEPART(DAY, ET) - DATEPART(DAY, ST)),
	[Hours]		= ABS(DATEPART(HOUR, ET) - DATEPART(HOUR, ST)),
	[Minutes]	= ABS(DATEPART(MINUTE, ET) - DATEPART(MINUTE, ST)),
	[Seconds]	= ABS(DATEPART(SECOND, ET) - DATEPART(SECOND, ST)),
	[Milliseconds]	= ABS(DATEPART(MILLISECOND, ET) - DATEPART(HOUR, ST))
from
	(
	select	-- Test Data
		ST = convert(datetime,'2008/09/22 00:35:33.997'),
		ET = convert(datetime,'2009/10/22 01:05:45.443')
	) a

-------------------
-- Results
-------------------
-- MJV
Days        Hours       Minutes     Seconds     Milliseconds
----------- ----------- ----------- ----------- ------------
395         0           30          11          447

-- Lamprey
Days        Hours       Minutes     Seconds     Milliseconds
----------- ----------- ----------- ----------- ------------
0           1           30          12          443

-- Note - Obviously, our DAY comparision is different.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-09-24 : 12:01:21

Lamprey -- run it with:

ST = convert(datetime,'2008/09/22 03:35:48.997'),
ET = convert(datetime,'2009/10/22 01:37:45.443')

and compare the two results ....

- Jeff
http://weblogs.sqlteam.com/JeffS

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2008-09-24 : 12:07:17

If you look, you can see that my code is exactly what Jeff shows in his article.

The basic idea is to let SQL Server compute the time difference as a datetime (EndTime-StartTime) that is an offset from 1900-01-01 00:00:00.000 and then parse the components of the difference from that.


select
	[Diff]		= ET-ST,
	[Days]		= datediff(day,0,ET-ST),
	[Hours]		= datepart(Hour,ET-ST),
	[Minutes]	= datepart(Minute,ET-ST),
	[Seconds]	= datepart(Second,ET-ST),
	[Milliseconds]	= datepart(millisecond,ET-ST)
from
	(
	select	-- Test Data
		ST = convert(datetime,'2008/09/22 00:35:33.997'),
		ET = convert(datetime,'2009/10/22 01:05:45.443')
	) a

Results:
Diff                    Days        Hours       Minutes     Seconds     Milliseconds 
----------------------- ----------- ----------- ----------- ----------- ------------ 
1901-01-31 00:30:11.447 395         0           30          11          447

(1 row(s) affected)

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources