Difference of two date time in sql server

Question

Hii,

Is there any way to take the difference between two date time in sql server for example my first date is '2010-01-22 15:29:55.090' second date is '2010-01-22 15:30:09.153'

Answer 1

SELECT DATEDIFF (MyUnits, '2010-01-22 15:29:55.090', '2010-01-22 15:30:09.153')

Substitute "MyUnits" based on DATEDIFF on MSDN

Answer 2

Check DateDiff out on Books Online.

Answer 3

SELECT  DATEDIFF(day, '2010-01-22 15:29:55.090', '2010-01-22 15:30:09.153')

Replace day with other units you want to get the difference in, like second, minute etc.

Answer 4

Just a caveat to add about DateDiff, it counts the number of times you pass the boundary you specify as your units, so is subject to problems if you are looking for a precise timespan. e.g.

select datediff (m, '20100131', '20100201')

gives an answer of 1, because it crossed the boundary from January to February, so even though the span is 2 days, datediff would return a value of 1 - it crossed 1 date boundary.

select datediff(mi, '2010-01-22 15:29:55.090' , '2010-01-22 15:30:09.153')

Gives a value of 1, again, it passed the minute boundary once, so even though it is approx 14 seconds, it would be returned as a single minute when using Minutes as the units.

Answer 5

Internally in SQL Server dates are stored as 2 integers. The first integer is the number of dates before or after the base date (1900/01/01). The second integer stores the number of clock ticks after midnight, each tick is 1/300 of a second.

More info here

Because of this, I often find the simplest way to compare dates is to simply substract them. This handles 90% of my use cases. E.g.,

select date1, date2, date2 - date1 as DifferenceInDays
from MyTable
...

When I need an answer in units other than days, I will use DateDiff.