I just happened to stumble upon this and couldn't find any technical explanation:
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, Level 16, State 2, Line 4
Explicit conversion from data type int to date is not allowed.
Why does the CAST from an empty string to DATE and DATETIME work, but from 0 it only works as DATETIME?
I'm interested in a technical explanation
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.
This works:
--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, Level 16, State 2, Line 5
Explicit conversion from data type numeric to date is not allowed.
It's maybe from timestamp behavioral.
You can cast 0 to datetime then cast to date.
Also this is a rule from Microsoft. An not a national rule.
I'm not sure there is much of clean technical explanation other than what SQL Server supports. SQL server ranks all of its data types Data Type Precendence. The implicit conversions have evolved over time. There is a Microsoft Blog by Craig Friedman that explains the logic and evolution pretty well More on Implicit Conversions. What @hamid_reza hobab indicates is essentially true. SQL server is supporting a conversion of an int to a datetime. Datetime is a higher precendence then date, so transitively datetime can be converted to the date. SQL server is not supporting an implicit or explicit conversion of int to date as indicated in the support matrix posted in the comment by @dfundako
