Search code examples
sqlsql-serverdatediffdateadd

DATEDIFF with a number instead of a date

I am having some trouble trying to understand how DATEDIFF should work. I saw in some cases an int argument instead of a valid date like the examples bellow:

/* Output is 119 */
SELECT DATEDIFF(year, 6, '2019/05/15');

/* Output is 6.227 */
SELECT DATEDIFF(week, 6, '2019/05/15');

In this case how number 6 is being treated? What does it mean?

Thanks!

Solution

  • This is an example of implict data type conversion. DATEDIFF() allows datetime as a parameter type, so 6 is converted implicitly to datetime and DATEADD() converts 0 to 1900-01-01.

    SELECT DATEDIFF(year, 6, '2019/05/15');
SELECT DATEDIFF(week, 6, '2019/05/15');

    is actually 

    SELECT DATEDIFF(year, DATEADD(day, 0, 6), '2019/05/15');
SELECT DATEDIFF(week, DATEADD(day, 0, 6), '2019/05/15');