DateTime 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

General SQL Server Forums

New to SQL Server Programming

DateTime difference

Author

Topic

cengiz_styla
Starting Member

5 Posts

Posted - 2011-05-03 : 10:22:31

Hi people, I want to get difference between 2 dates.

I have 2 tables en columns with Datetype datatype and i want to get with SQL the difference of these 2 dates. The values of these 2 columns look like MM/dd/YYYY HH:mm:ss.. I want to compare these 2 columns at one time not with the function DATEDIFF().. Because with this function I must compare one by one and you get not the exact solution.
Example; When I have for the startdate 5/3/2011 9:45 and endtime 5/3/2011 10:01 I get with datediff(hour) = 1 and datediff(minute)= 6.. Datediff(minute) is true but datediff(hour) not!. e
Example 2; Startdate: 12/31/2010 23:59 - enddate: 1/1/2011 00:00, you will get with datediff funcion for day:1, month:1, hour: 1, hour:1, min:1 and this is not what I want. The difference between those 2 dates is 1 minute!

To prevent this, is there a better way to solve or get it how I want ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-05-03 : 10:45:35

depending on the value of the first parameter, datediff() will always return the different in value based on the first parameter.

Exmaple as you have already found out,

datediff(month, '2011-04-30 00:20', '2011-05-01 00:50') will give you 1

so if the accuracy you required is in hour, then

datediff(hour, '2011-04-30 00:20', '2011-05-01 00:50') will give you 24

or if you want it in hour but with decimal

datediff(minute, '2011-04-30 00:20', '2011-05-01 00:50') / 60.0

You may also want to read this article
http://www.sqlteam.com/article/datediff-function-demystified

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources