为什么Datediff函数显示不同的值？

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

当我执行以下查询时，我得到不同的结果。

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

The result was 115

结果是115

When I use this, I am getting another result:

当我使用它时，我得到另一个结果：

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

The result was 110

结果是110

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

实际上在SQL Server中它将需要从1900-01-01，但为什么这些显示不同的值？

3 个解决方案

#1

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-01-01以来的天数，而字符串值（如'1900'）将被解释为日期格式。

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

从1900年1月1日起的1900天是1905-03-16，这是从1900年起的5年和从现在起的110年（2015年）。

#2

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.

这是因为如果您将0转换为日期时间，则返回1900作为年份部分，而1900年作为datetime返回1905作为年份部分。

Demo

演示

From MSDN:

来自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.

具有datetime数据类型的值由Microsoft SQL Server在内部存储为两个4字节整数。前4个字节存储基准日期（1900年1月1日）之前或之后的天数。基准日期是系统参考日期。

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.

这意味着，将文字0转换为datetime相当于在1/1/1900（即1/1/1900）之后获取0天的datetime值。类似于1900年。因此，正如@MartinSmith在评论中指出的那样，你的计算相当于SELECT Datediff（年，日期（d，0，'1/1/1900'），Getdate（）），它按预期返回115。

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

可能值得注意的是，Cast和Convert上的MSDN页面并未特别涵盖此场景，即int到datetime。

#3

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.

Result1是1900 Result2是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());;

#1