CODEWITHSUNDEEP
sql-servert-sqldate
JimP
JimP

Reputation: 135

Why does DATEDIFF return -1

I'm trying to understand how the DATEDIFF function works in SQL Server. The following T-SQL returns -1:

SELECT DATEDIFF(MONTH, 2015-10-25, 2015-12-27)

However, ending_date is after start_date so I would expect a positive number, not a negative number. Further, I would expect that the difference between Oct 25 and Dec 27 to be 2 months. Instead, SQL Server is returning -1 month. Can someone explain what's going on?

Upvotes: 0

Views: 1030

Answers (4)

Zohar Peled
Zohar Peled

Reputation: 82524

You are missing apostrophes:

SELECT  2015-10-25 AS First, 
        2015-12-27 AS Second,
        CAST(2015-10-25 As DateTime) AS [First as datetime],
        CAST(2015-12-27 As DateTime) AS [Second as datetime],
        DATEDIFF(MONTH, 2015-10-25, 2015-12-27) AS WrongResult,
        DATEDIFF(MONTH, '2015-10-25', '2015-12-27') AS CorrectResult

Results:

First   Second  First as datetime       Second as datetime      WrongResult CorrectResult
1980    1976    04.06.1905 00:00:00     31.05.1905 00:00:00     -1          2

SQL Server looks at 2015-10-25 as an int - the result of the mathematical expression (1980).
The usage of int in datetime functions cause SQL Server to implicitly convert the int value to a DateTime value.
The int value represents the number of days since '1900-01-01' - and since the second argument results in a smaller int value, you get a negative number.

As Jeroen Mostert wrote in his comment - A gotcha for the books.

Upvotes: 6

HoneyBadger
HoneyBadger

Reputation: 15150

You are not quoting your dates, this means your expression evaluates to:

SELECT DATEDIFF(MONTH, 1980, 1976)

Which after implicit conversion to datetime evaluates to:

SELECT DATEDIFF(MONTH, '1905-06-04 00:00:00.000', '1905-05-31 00:00:00.000')

So now the startdate is after the enddate.

Upvotes: 5

Thom A
Thom A

Reputation: 95924

Because you're using numbers (integers specifically), not strings. 2015-10-25 = 1980 which, when you convert to a date is '1905-06-04'. On the other hand 2015-12-27 = 1976, which as a date is '1905-05-31'. As '1905-05-31' is one month before '1905-06-04' you get the result -1.

Use literal strings, and yyyyMMdd dates:

SELECT DATEDIFF(MONTH, '20151025', '20151227');

Upvotes: 2

Ullas
Ullas

Reputation: 11556

Put the date values within single quotes.

Query

SELECT DATEDIFF(MONTH, '2015-10-25', '2015-12-27');

This query returns 2 as the output.

Upvotes: 1

Related Questions