Why is that?

History. It was allowed and caused all sorts of issues. MS decided not to support that functionality when it introduced those newer datatypes - and they also added a number of functions related to the date and time datatypes that are improvements over the workarounds that were needed in older versions of sql server.

• Proposed as answer by Naomi NModerator 15 hours 51 minutes ago
• Unproposed as answer by J I M B O 15 hours 18 minutes ago

Microsoft is trying to get rid of the old T-SQL Sybase/UNIX crap and get over to ANSI/ISO Standards.

CELKO is there a Microsoft link that states that Microsoft is moving towards the ANSI/ISO standards?  For the DATETIME2 data type, it appears that way.  But, it could also be that they are just supporting interoperability between the various database systems out there.  Actually, on the DATETIME page on BOL, it states that DATETIME2 is preferable over DATETIME due to its alignment to the SQL Standard, portability, and precision.

However, I am more interested in your statement that Microsoft is moving towards the ANSI/ISO standards as a whole.  I rather not state that as fact unless I have something more concrete.

Thanks ahead of time.

• Edited by J I M B O 16 hours 54 minutes ago Removed unnecessary text from quote.

History. It was allowed and caused all sorts of issues. MS decided not to support that functionality when it introduced those newer datatypes - and they also added a number of functions related to the date and time datatypes that are improvements over the workarounds that were needed in older versions of sql server.

Is there an article supporting this statement?  I'm not arguing that it's not correct.  I just prefer not to state it as fact without something more concrete.

Explicit conversion from data type int to date, time, datetimeoffset, and datetime2 is not allowed.

• SQL Server Data Type Conversion Chart
• Cast and Convert

Thanks for responding Jose.  I may not have been very clear in my post.  I understand that the cast is not allowed.  I am more interested in the history and reasoning why an integer conversion of 0 to DATETIME2 or DATE is not allowed when it is for DATETIME and SMALLDATETIME.

• Edited by J I M B O 16 hours 53 minutes ago Removed unnecessary text from quote.

Here is another interesting find using the queries below against the master database of a SQL Server 2012 instance.  The master database is also in SQL Server 2012 compatibility.  Keep in mind that the current date is 2015-Jul-23.

-- Produces 2015-07-23 00:00:00.000
SELECT
    DATEADD(DAY, DATEDIFF(DAY, '19000101', SYSDATETIME()), '19000101')

-- Produces an error
SELECT
    DATEADD(DAY, DATEDIFF(DAY, '00010101', SYSDATETIME()), '00010101')

-- Produces 735801
SELECT
    DATEDIFF(DAY, '00010101', SYSDATETIME())

The error message for the second SQL statement is the following:

"The conversion of a varchar data type to a datetime data type resulted in an out-of-range value."

I find this interesting since the BOL for DATEDIFF and DATEADD states that the date parameter is "an expression that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset value."

There may be an error in BOL since the SQL message is expecting a DATETIME for the date parameter?

Good day J I M B O,

There is no mistake in the BOL and not a bug regarding this issue. You did not use any type like "time,date, smalldatetime, datetime, datetime2, or datetimeoffset value." but string!

Please check this short blog regarding the difference between displaying format, vs Date storing format. 

DATEDIFF(DAY, '00010101', SYSDATETIME())
* '00010101' This is string which represent another type using displaying format. The SQL Server try to convert it implicitly... and error show it did not succeed for a simple reason (as you can see in the blog)

My books Online states, for DATEADD:

"Is an expression that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset value. date can be an expression, column expression, user-defined variable, or string literal. If the expression is a string literal, it must resolve to a datetime."

Note the last sentence. It seems that the behaviour as well as error message acts according to the documentation. Here is something that doesn't give an error, also as expected:

SELECT DATEADD(DAY, 735801, CAST('00010101' AS date))

Jimbo, regarding your both requests for supporting documentation, I doubt you will find such. Sometime you will just trust sound reasoning and people that has been around for a long time and worked with the stuff for ages. 

ANSI SQL, for instance: This is so obvious, that MS are trying to steer towards ANSI SQL whenever possible - unless they have good reasons not to. Why wouldn't they? This falls under the "obvious" category to me.

As for above: we'd had to be a fly on the wall in the meeting where the product group decided on the spec's for the product. This leads to some "educated guesses", which is the category I put this in.

Not what you wanted to here, just my perspective who get a lot of these types of questions over the some 20 years I've been training in SQL Server. Sometimes you just don't have the exact answer, but educated guesses etc aren't that bad. :-)

My books Online states, for DATEADD:

"Is an expression that can be resolved to a time, date, smalldatetime, datetime, datetime2, or datetimeoffset value. date can be an expression, column expression, user-defined variable, or string literal. If the expression is a string literal, it must resolve to a datetime."

Good job Tibor!  I looked up the BOL for DATEDIFF and pasted that.  I glanced through the explanation for DATEADD and saw that the first sentence was identical and didn't read the rest.  The issue was with the DATEADD and not the DATEDIFF.

Jimbo, regarding your both requests for supporting documentation, I doubt you will find such. Sometime you will just trust sound reasoning and people that has been around for a long time and worked with the stuff for ages. 

Actually, I get it from being asked questions about the ANSI/ISO stuff by the developers at Microsoft I kno

SQL Server 2016 do not show that this is the future as much as I can see. In my opinion, There is an attempt from all companies to come as close as they can to standards but NOT on the cost of performance or features. Therefore you might find some feature get closer to the standarts while other go to other side. Standards are not the goal! but only a tools and CELKO do not remember this I think.

* DateTime class in .Net does not fit DATETIME in SQL Server (range for example), and there was a need for DATETIME2 which is more fit to the DateTime class of .Net. Yes it is also more fit to the standarts :-). Moreover it is much better type than SQL Server DATETIME. You can read more about Datetime2 and the internal of it here. You should not use DATETIME but DATEWTIME2 always if you do not need backward compatibility.