CODEWITHSUNDEEP
sqlsql-server
Sai Praveen
Sai Praveen

Reputation: 223

Why does the Datediff function show different values?

When I am executing following query I am getting different results.

SELECT Datediff(year, 0, Getdate());

The result was 115

When I use this, I am getting another result:

SELECT Datediff(year, 1900, Getdate());

The result was 110

Actually in SQL Server it will take from 1900-01-01, but why do these show different values?

Upvotes: 23

Views: 1964

Answers (3)

Azar
Azar

Reputation: 1867

The number you specified will be added as days which resulted in the difference.

Select DATEADD(dd,0,0)
Select DATEADD(dd,1900,0)

Result1 is 1900 Result2 is 1905.

So using them is equal to:

SELECT Datediff(year,0, Getdate()) = SELECT Datediff(year,DATEADD(dd,0,0), Getdate());

SELECT Datediff(year,1900, Getdate()) = SELECT Datediff(year,DATEADD(dd,1900,0), Getdate());;

Upvotes: 3

shree.pat18
shree.pat18

Reputation: 21757

This is because if you cast 0 as datetime, it returns 1900 as the year part, whereas 1900 cast as datetime returns 1905 as the year part.

Demo

From MSDN:

Values with the datetime data type are stored internally by Microsoft SQL Server as two 4-byte integers. The first 4 bytes store the number of days before or after the base date, January 1, 1900. The base date is the system reference date.

That means, casting the literal 0 to datetime is equivalent to getting the datetime value for 0 days after 1/1/1900, which is 1/1/1900. Similarly for 1900. Therefore, as @MartinSmith points out in the comments, your calculation is equivalent to SELECT Datediff(year,dateadd(d,0,'1/1/1900'), Getdate()) which returns 115 as expected.

Possibly worth noting that the MSDN page on Cast and Convert does not specifically cover this scenario i.e. int to datetime.

Upvotes: 15

SAS
SAS

Reputation: 4045

Try this to explain the logic:

select cast(0 as datetime)
select cast(1 as datetime)

An integer is interpreted as the number of Days since 1900-01-01 whereas a string value such as '1900' will be interpreted as a date format.

1900 Days from Jan 1st 1900 is 1905-03-16, which is five years from 1900 and 110 years from now (2015).

Upvotes: 23

Related Questions