将0转换为DATE和DATETIME [英] Casting 0 as DATE and DATETIME

问题描述

我只是偶然发现了这一点，找不到任何技术解释：

I just happened to stumble upon this and couldn't find any technical explanation:

在SQL Server 2014中：

In SQL Server 2014:

SELECT CAST('' AS DATETIME);
1900-01-01 00:00:00.000

SELECT CAST(0 AS DATETIME);
1900-01-01 00:00:00.000

SELECT CAST('' AS DATE);
1900-01-01

SELECT CAST(CAST(0 AS DATETIME) AS DATE);
1900-01-01

SELECT CAST(0 AS DATE);

Msg 529，第16层，状态2，第4行

不允许从数据类型int到日期的显式转换。

Msg 529, Level 16, State 2, Line 4
Explicit conversion from data type int to date is not allowed.

为什么 CAST 从一个空字符串到 DATE 和 DATETIME 起作用，但从 0 只能用作 DATETIME ？

Why does the CAST from an empty string to DATE and DATETIME work, but from 0 it only works as DATETIME?

我对技术说明感兴趣

推荐答案

我认为这与Microsoft选择支持的问题有关。或多或少地归结为以下事实：允许从数值将数据转换为日期时间，而日期不允许从数值进行转换。在SQL Server中，它必须先将0转换为0.000000000之类的数值，然后再转换为该数字的datetime。我的示例表明，可以将当前日期转换为数字值，然后再转换为日期时间。但是，转换为日期会引发错误。我想避免如果尝试将值0.5转换为日期时可能会出现的舍入问题。在这种情况下，将对SQL引擎进行编程以将0.5隐式转换为日期1900-01-01或1900-01-02。在这种情况下，您必须对应该返回什么日期做出任意决定。

I think it's a matter of what Microsoft chose to support. More or less it comes down to the fact that data conversions to datetime are allowed from numeric values, while date does not allow conversions from numeric values. Within SQL server, it must be converting the 0 first to a numeric value like 0.000000000 and then to the datetime equivalent of that number. My example shows that it's possible to convert the current date into a numeric value and then back to a datetime. However, converting to a date throws an error. I would guess to avoid rounding issues that you may have if you tried to convert the value 0.5 to a date. Would program the SQL engine to implicitly convert 0.5 to the date 1900-01-01 or 1900-01-02 in that case. You'd have to make arbitrary decisions on what date should be returned in that case.

这有效：

--produces a numeric value like 42746.97660799
select cast(getdate() as numeric(18,8)) 

select cast(42746.97660799 as datetime)

这会引发与您收到的错误相同的错误：

While this throws the same error you received:

select cast(42746.97660799 as date)

Msg 529，级别16，状态2，第5行
来自数据的显式转换不允许输入迄今为止的数字。

Msg 529, Level 16, State 2, Line 5 Explicit conversion from data type numeric to date is not allowed.

这篇关于将0转换为DATE和DATETIME的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！