DATEDIFF with examples

DATEDIFF with examples

DATEDIFF function accepts 3 parameters, first is datepart (can be an year, quarter, month, day, hour etc.) and rest are two dates which you want to compare.

Few examples of DATEDIFF:

DATEDIFF - Example 1
Here, in this example, datepart is "day":

SELECT DATEDIFF(day,'2016-06-05','2016-08-05') AS DiffDate

Result:
DiffDate
61

DATEDIFF - Example 2
datepart is "hour" and it is calculating difference between today's date 
and yesterday's date:

SELECT DATEDIFF(HOUR,getdate()-1,getdate()) AS DiffDate

Result:
DiffDate
24

DATEDIFF - Example 3
another example of how you can write the dates:

SELECT DATEDIFF(day,'20160605','20160805') AS DiffDate

Result:
DiffDate
61

DATEDIFF - Example 4
you can also write only two digits for an year:

SELECT DATEDIFF(day,'160605','160805') AS DiffDate

Result:
DiffDate
61

DATEDIFF - Example 5
less then two digits for an year (or month/day)  will throw an error:

SELECT DATEDIFF(day,'40605','50805') AS DiffDate

Result:
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.

DATEDIFF - Datepart boundaries

As per MSDN, DateDIFF returns the count (signed integer) of the specified datepart boundaries crossed between the specified startdate and enddate.

For example if both startdate and endate are in the same calendar week, the return value for week would be 0.

DATEDIFF - Example 6
SELECT DATEDIFF(WEEK, '2016-06-15', '2016-06-17');

Result:
(No column name)
0

In the same way boundary for months is the 1st day of the month, boundary for years is the 1st of January.

DATEDIFF - Example 7
Below three examples would return 1, because we have only one 1st of January between 
both ranges of dates below.

Select DATEDIFF(Year, '2015-04-23', '2016-04-23')

Result:
(No column name)
1

DATEDIFF - Example 8
Select DATEDIFF(Year, '2015-01-01', '2016-12-31')

Result:
(No column name)
1

DATEDIFF - Example 9
SELECT DATEDIFF(year, '2015-12-31 23:59:59.9999999', '2016-01-01 00:00:00.0000000');

Result:
(No column name)
1

DATEDIFF DEFAULT VALUES

If only a time value is assigned to a variable of a date data type, the value of the missing date part is set to the default value: 1900-01-01. If only a date value is assigned to a variable of a time or date data type, the value of the missing time part is set to the default value: 00:00:00. If either startdate or enddate have only a time part and the other only a date part, the missing time and date parts are set to the default values.

DATEDIFF - Example 10
SELECT DATEDIFF(MONTH,'1900-01-01' , GETDATE());

Result:
(No column name)
1398

DATEDIFF - Example 11
SELECT DATEDIFF(MONTH,'' , GETDATE());

Result:
(No column name)
1398

DATEDIFF - day and dayofyear datepart values seems similar

DATEDIFF - Example 12
select DATEDIFF(dayofyear, '2016-01-01', '2016-02-02')

Result:
(No column name)
32

DATEDIFF - Example 13
select DATEDIFF(day, '2016-01-01', '2016-02-02')

Result:
(No column name)
32

DATEDIFF sometimes returns wrong results in case where day in starting date is later than the day in the ending date.

Example 14 & 15 give wrong results

DATEDIFF - Example 14
select DATEDIFF(year,'2013-05-18', '2014-01-01')

Result:
(No column name)
1

DATEDIFF - Example 15
select DATEDIFF(MONTH,'2013-05-18', '2014-01-01')

Result:
(No column name)
8

Below is the way of how you can use DATEDIFF to produce correct results:

DATEDIFF - Example 16
select (DATEDIFF(YEAR,'2013-05-18', '2014-01-01')-1)/12

Result:
(No column name)
0

DATEDIFF - Example 17
select DATEDIFF(MONTH,'2013-05-18', '2014-01-01')-1

Result:
(No column name)
7

Some more examples of wrong results and how these can be corrected:

DATEDIFF - Example 18 -- wrong result
select DATEDIFF(year,'2016-06-28', '2017-01-01')

Result:
(No column name)
1

DATEDIFF - Example 19 -- wrong result
select DATEDIFF(MONTH,'2016-06-30', '2016-08-02')

Result:
(No column name)
2

DATEDIFF - Example 20 -- correct result
select (DATEDIFF(year,'2016-06-28', '2017-01-01')-1)/12

Result:
(No column name)
0

DATEDIFF - Example 21 -- correct result
select DATEDIFF(MONTH,'2016-06-30', '2016-08-02')-1

Result:
(No column name)
1