Casting 0 to SMALLDATETIME or DATETIME is allowed in T-SQL which produces to 1900-01-01.  However, you can't cast 0 to DATE or DATETIME2.  Why is that?

• Edited by J I M B O 8 hours 33 minutes ago Reworded post

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

• SQL Server Data Type Conversion Chart
• Cast and Convert

To expand on this I would assume it has to do with the confusion caused when dealing with Excel dates with the old datatypes.

Essentially your Excel dates will end up two days out if you let SQL Server do that sort of implicit conversion (you can do a search for why this is if you are interested)

Because it is insane. On a scale from 1 to 10 what color is your favorite letter of the alphabet? 

Microsoft is trying to get rid of the old T-SQL Sybase/UNIX crap and get over to ANSI/ISO Standards. This means the date range is '0001-01-01' to '9999-12-31' today. If you wrote code that depended on the old DEC PDP hardware with the old DATETIME kludge, you need to make some major corrections to that code with your next upgrade. Do it now, and comment out the DDL and DML patches, so that you have them in place when you need to go live. 

To expand on this I would assume it has to do with the confusion caused when dealing with Excel dates with the old datatypes.

Essentially your Excel dates will end up two days out if you let SQL Server do that sort of implicit conversion (you can do a search for why this is if you are interested)

Similarly in SSIS also the base date is different

Which is why its safe to use date functions like DATEADD to perform manipulation of dates rather than doing integer arithmetic even for older date datatypes like datetime

http://visakhm.blogspot.ae/2013/10/ssis-base-date-t-sql-base-date.html