DATEDIFF Function Demystified

By Peter Larsson on 20 March 2007 | Tags: Data Types , Functions

This article comes to us from Peter Larsson also known as Peso in the forums. He writes "I have seen many questions about the DATEDIFF function where people are baffled about why it doesn't calculate the correct result. The short answer is 'It does'." Peter discusses the DATEDIFF function and provides two functions that calculate years and months the way you think it should. Thanks Peter!

How am I so sure about that?

Because the people asking these questions do not understand the DatePart parameter! The trouble begins with the false assumption that the DATEDIFF function should calculate the full difference between two dates in either YEAR, MONTH or DAY (or any other valid DatePart parameter value).

The DATEDIFF function does not work that way. The function works in what I like to call "slots".

Sample picture for Year difference

The red curve exemplifies the two dates February 17, 2000 and December 13, 2008. According to human mind, this should be {February 17, 2001; February 17, 2002; February 17, 2003; February 17, 2004; February 17, 2005; February 17, 2006; February 17, 2007; February 17, 2008} which is 8 years (and some months more).

What does the DATEDIFF function tell us?

See the start date of February 17, 2000. Since we choose Year as the value for the DatePart parameter, SQL Server chooses the full year 2000 as starting year (year 2000 being the starting "slot"). The same thing happens with the ending date of December 13, 2008. SQL Server chooses 2008 as ending year (year 2008 being the ending "slot") again because we have set Year as the DatePart parameter value. And now the difference should be {February 17, 2001; February 17, 2002; February 17, 2003; February 17, 2004; February 17, 2005; February 17, 2006; February 17, 2007; February 17, 2008} which is 8 years difference, according to the DatePart parameter value of Year. What we think (how the human mind works) and how the DATEDIFF function works are equal.

But what if the ending date is earlier in the ending year than the starting date is in the starting year?

The blue curve exemplifies the two dates September 10, 2002 and March 5, 2006. According to the human mind, this should be a difference of {September 10, 2003; September 10, 2004; September 10, 2005} which is 3 years because September 10, 2006 has not occurred in the ending year.

But does the DATEDIFF function work this way?

See the start date of September 10, 2002. Since we chose Year as the value for DatePart parameter SQL Server chooses the full year 2002 as starting year (year 2002 being the starting "slot"). The same thing happens with the ending date of March 5, 2006. SQL Server chooses 2006 as the ending year (year 2006 being the ending "slot") because we have set Year as the DatePart parameter value. And now the difference in Years is {September 10, 2003; September 10, 2004; September 10, 2005; September 10, 2006} which is 4 years difference!

I can demonstrate this for you with another DatePart parameter value. Let's choose Month.

Sample picture for Month difference

The yellow curve exemplifies the two dates May 2 and July 29. According to the human mind this should be a difference of {June 2, July 2} which is 2 months (and some days more).

What does the DATEDIFF function tell us?

See the start date of May 2. Since we choose Month as the value for the DatePart parameter SQL Server chooses the full month of May as the starting month (May is the starting "slot"). The same thing happens with July 29. SQL Server chooses July as the ending month (July being the ending "slot") because we have set Month as the DatePart parameter value. And now the difference in Months is {June 2, July 2} which is also 2 months difference.

What we think (how the human mind work) and how the DATEDIFF function works are equal.

But what if the ending date is earlier in the ending month than the starting date is in the starting month?

The blue curve exemplifies the two dates May 25 and July 4. According to the human mind, this should be a difference of {June 25} which is 1 month, because July 25 has not occurred in the ending month, right?

But does the DATEDIFF function work this way?

See the start date of May 25. Since we chose Month as the value for the DatePart parameter, SQL Server chooses the full month of May as the starting month (May being the starting "slot"). The same thing happens with July 4. SQL Server chooses July as the ending month (July being the ending "slot") because we have set Month as the DatePart parameter value. And now the difference in Months is {June 25, July 25} which is 2 months difference!

Examples of this behavior

Copy this test code to a query window and run the code.

-- Prepare sample data
DECLARE    @Dates TABLE (StartingDate DATETIME, EndingDate DATETIME)

INSERT     @Dates
SELECT     '20010101', '20070204' UNION ALL
SELECT     '20071231', '20080101' UNION ALL
SELECT     '20070318', '20080101'

-- Show the expected output

SELECT     StartingDate,
           EndingDate,
           DATEDIFF(MONTH, StartingDate, EndingDate) AS [Month DatePart],
           DATEDIFF(YEAR, StartingDate, EndingDate) AS [Year DatePart]
FROM       @Dates
ORDER BY   StartingDate

You will get an output like this:

Starting date            Ending date                Diff in Months  Diff in Years
-----------------------  -----------------------    --------------  -------------
2001-01-01 00:00:00.000  2007-02-04 00:00:00.000    73              6
2007-03-18 00:00:00.000  2008-01-01 00:00:00.000    10              1
2007-12-31 00:00:00.000  2008-01-01 00:00:00.000    1               1

As you can see the DATEDIFF functions returns the correct result for line 1 for both Month and Year. But for line 2 and line 3 the DATEDIFF functions returns incorrect values according to how we have seen DATEDIFF behave.

Line 2 should have the Month value of {April 18; May 18; June 18; July 18; August 18; September 18; October 18; November 18; December 18} which is 9 full months and the year value of 0.

Line 3 is even worse. It should have the Month value of {} which is 0 full months. And the Year value of {} which is 0 full years.

How can we correct this behavior?

As seen, this unexpected behavior only occurs in cases where day in starting date is later than the day in the ending date.

For Year as DatePart parameter value, this SQL function works as we think the DATEDIFF function should work, with full years passed.

CREATE FUNCTION dbo.fnYearsApart
(
        @FromDate DATETIME,
        @ToDate DATETIME
)
RETURNS INT
AS
BEGIN
        RETURN  CASE
                       WHEN @FromDate > @ToDate THEN NULL
                       WHEN DATEPART(day, @FromDate) > DATEPART(day, @ToDate) THEN DATEDIFF(month, @FromDate, @ToDate) - 1
                       ELSE DATEDIFF(month, @FromDate, @ToDate)
               END / 12
END

For Month as DatePart parameter value, this SQL function works as we think the DATEDIFF function should work with full months passed.

CREATE FUNCTION dbo.fnMonthsApart
(
        @FromDate DATETIME,
        @ToDate DATETIME
)
RETURNS INT
AS
BEGIN
        RETURN  CASE
                       WHEN @FromDate > @ToDate THEN NULL
                       WHEN DATEPART(day, @FromDate) > DATEPART(day, @ToDate) THEN DATEDIFF(month, @FromDate, @ToDate) - 1
                       ELSE DATEDIFF(month, @FromDate, @ToDate)
               END
END