Join Us!

Spaces are allowed; punctuation is not allowed except for periods, hyphens, and underscores.
A valid e-mail address. All e-mails from the system will be sent to this address. The e-mail address is not made public and will only be used if you wish to receive a new password or wish to receive certain news or notifications by e-mail.
CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
Image CAPTCHA
Enter the characters shown in the image.

Login

Enter your Project Envision username.
Enter the password that accompanies your username.
Request new password

Calculating Date Difference in Sql Server

Often we need to calculate the difference between two dates in Sql Server. Microsoft provided us with DateDiff function, which is very handy in such situations. The most often problem with DateDiff function is with its DatePart parameter.

DateDiff function takes three parameters, DatePart specifies on which part of the date is used in calculation, StartDate specifies starting date and EndDate the ending date.

DateDiff(DatePart, StartDate, EndDate)

The basic example of using DateDiff, shows how to calculate the difference between twelfth of February 2006 and fifth of July 2009. The return value is 3, because as a DataPart parameter I provided year, which means that I am interested in year difference.

select DateDiff(year,'2006-02-12','2009-07-05')
3

What will happen if I choose the fifth of January 2009, instead of the fifth of July 2009, some people could think that returned value would be two, because the fifth of January is before twelfth of February. But they will be wrong, DateDiff function works based on slots, so that all more precise parts of the date are being ignored. If we select year as a DatePart argument all other parts of date except year are being ignored. If we select month as a DatePart only year and month part will be used, more precise parts of the date won’t be used in calculation.

select DateDiff(year,'2006-02-13','2009-01-05')
3

Let’s prove that point on another example, this time we will use month as a DatePart argument.

select DateDiff(month,'2009-05-20','2009-07-05')
2

The result is 2, because Sql Server chosen May as it starting month and July as its ending month. Now we will change the year in starting from 2009 to 2008.

select DateDiff(month,'2008-05-20','2009-07-05')
14

If EndDate is before StartDate the DateDiff function will return negative integer.

select DateDiff(year,'2009-12-01','2007-01-04')
-2
select DateDiff(month,'2009-05-20','2008-07-05')
-10

Except the year and month we can use the following dateparts.
Quarter, which is used to calculate the differences in year’s quarters.

select DateDiff(quarter,'2009-01-01','2009-02-01')
0

select DateDiff(quarter,'2009-01-01','2009-07-01')
2

Dayofyear

select DateDiff(dayofyear,'2009-01-01','2009-07-01')
181

select DateDiff(dayofyear,'2009-01-01','2009-01-03')
2

Day

select DateDiff(day,'2009-01-01','2009-07-01')
181

select DateDiff(day,'2009-01-01','2009-01-03')
2

Week

select DateDiff(week,'2009-01-01','2009-07-01')
26

select DateDiff(week,'2009-01-01','2009-01-03')
0

Hour

select DateDiff(hour,'2009-01-01 9:00:00','2009-01-03 13:05:45')
52

Minute

select DateDiff(minute,'2009-01-01 9:00:00','2009-01-01 13:05:45')
245

Second

select DateDiff(second,'2009-01-01 9:00:00','2009-01-01 13:05:45')
14745

Millisecond

select DateDiff(millisecond,'2009-01-01 13:05:45:12','2009-01-01 13:05:45:45')
33