CODEWITHSUNDEEP
sqlsql-serverdatediffdateadd
briba
briba

Reputation: 2987

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!

Upvotes: 0

Views: 4929

Answers (2)

Zhorov
Zhorov

Reputation: 29993

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');

Upvotes: 4

user11380812
user11380812

Reputation:

If only a time value is assigned to a date data type variable, DATEDIFF sets the value of the missing date part to the default value: 1900-01-01. So, the following snippet returns 119 ( 6 = 7th January 1990 )

SELECT DATEDIFF(year, '1900/01/07', '2019/05/15');

Upvotes: 1

Related Questions