Search code examples
sqlsql-serverssms-2014

SQL DATEDIFF function

I am working on SQL server using SSMS 2014

select DATEDIFF(MM, 0, GETDATE())

gives output as 1409 (run on 16-06-2017)

I am confused because DATEDIFF syntax's 2nd and 3rd parameter should be a valid date but here how and why is this query executing and giving result when 2nd parameter is 0.

I checked ISDATE(0) and ISDATE('0') which gives syntax error , so if 0 is not a valid date then why DATEDIFF is accepting it and giving result.

Solution

  • The documentation specifies:

    startdate

    Is an expression that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset value.

    Although a subtle distinction, "can be resolved" is not the same as "is the type of".

    So, an integer is interpreted as the number of days since '1899-12-31'. However, strings are not interpreted as days, even when they look like a number.

    ISDATE() takes a string argument, so it is converting 0 to '0'. And '0' is not a date format. In fact, datediff() will fail with the second argument passed as '0' rather than 0.

    And (although pretty much never used), any integer will work as the second argument.