Date difference - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Date difference

Author

Topic

Tasta
Yak Posting Veteran

60 Posts

Posted - 2009-06-25 : 08:13:18

Hi !
I have to receive difference in hours between 2 dates, for example
SELECT DATEDIFF(day, '2009-06-26 12:00', '2009-06-25 12:59')
that gives me 0 hours and in fact it's 1 hour.
I tried DATEDIFF(minute, '2009-06-25 10:00', '2009-06-25 10:59')/60
but it returns integer, so it's 0 again. Is there a way to receive rounded difference like for numbers ?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-25 : 08:17:52

DATEDIFF(minute, '2009-06-25 10:00', '2009-06-25 10:59')/60.0

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-25 : 08:18:32

See here how DATEDIFF works. It's not mysterious at all...
http://www.sqlteam.com/article/datediff-function-demystified

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

Tasta
Yak Posting Veteran

60 Posts

Posted - 2009-06-25 : 08:29:37

Thank you very much, Peso !
I've spent half a day looking for this answer and this article !

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-06-25 : 12:45:12

Depending on your need for accuracy, the DiffInHours2 method below will give a more accurate result if the difference in time is less than 24 days.


Select
	DiffInHours1 = datediff(minute,DT1,DT2)/60.00,
	DiffInHours2 = datediff(ms,0,DT2-DT1)/3600000.00
from
	(
	select
		DT1 = convert(datetime,'2009-06-25 10:00:01.847'),
		DT2 = convert(datetime,'2009-06-25 10:59:59.333')
	)  a

Results:
DiffInHours1         DiffInHOurs2             
-------------------- ------------------------ 
.983333              .9993016666

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources