Date conversion and culture: Difference between DATE and DATETIME

I've written a lot of answers about date or datetime conversions from strings. Living in a german speaking country, I'm used to deal with non-us_english date formats and I'm used to use secure literals (I prefere the ODBC format) and I never use CONVERT without the third parameter. That is not the question and please do not provide answers in this direction...

Very often one can read, that a format yyyy-mm-dd is standard (ISO8601, ANSI, whatever) and therefore culture independant.

Today I had to edit one of these older answers, as I had stated there, that the observed behaviour is depending on something else.

The question is:

Why (if there is a reason) is there a difference between DATE and DATETIME?

... at least in my environmen, which is SQL Server 2014 (12.0.4237.0) at the moment.

I hope, this was not asked before...

Try this:

No problems here, DATE works as expected

SET LANGUAGE ENGLISH;
DECLARE @dt DATE='2017-01-13'; 
SELECT @dt;
SELECT CAST('2017-01-13' AS DATE);
SELECT CONVERT(DATE,'2017-01-13'); --no culture / format specified
GO
SET LANGUAGE GERMAN;
DECLARE @dt DATE='2017-01-13';
SELECT @dt;
SELECT CAST('2017-01-13' AS DATE);
SELECT CONVERT(DATE,'2017-01-13'); 

But now check the same with DATETIME

--No problem here:
SET LANGUAGE ENGLISH;
DECLARE @dt DATETIME='2017-01-13'; 
SELECT @dt;
SELECT CAST('2017-01-13' AS DATETIME);
SELECT CONVERT(DATETIME,'2017-01-13'); 
GO

--breaks, due to the "13" and would deliver a wrong result (even worse), if the "day" was not more than "12":
SET LANGUAGE GERMAN;
DECLARE @dt DATETIME='2017-01-13'; 
SELECT @dt;
SELECT CAST('2017-01-13' AS DATETIME);
SELECT CONVERT(DATETIME,'2017-01-13'); 

Is this a bug, purpose or just grubbiness?

The ISO-8601 for DATETIME (the older type) is somehow "broken" or "adapted" (depending on whether you look at it as a bug or a feature) - you need to use YYYYMMDD (without any dashes) to make it work irrespective of the language settings.

For DATE or the DATETIME2(n) datatypes, this has been fixed and the "proper" ISO-8601 format YYYY-MM-DD will always be interpreted correctly.

-- OK because of "adapted" ISO-8601
SET LANGUAGE GERMAN;
DECLARE @dt DATETIME='20170113'; 

SELECT @dt;

SELECT CAST('20170113' AS DATETIME);
SELECT CONVERT(DATETIME, '20170113'); 

-- OK because of DATETIME2(n)
SET LANGUAGE GERMAN;
DECLARE @dt2 DATETIME2(0) = '2017-01-13'; 

SELECT @dt2;

SELECT CAST('2017-01-13' AS DATETIME2(0));
SELECT CONVERT(DATETIME2(0), '2017-01-13'); 

It's a quirk of the DATETIME type (and not the only one....) - just register it, know about it - and move on (meaning: don't use DATETIME anymore - use DATE or DATETIME2(n) instead - much nicer to work with!) :-)

(Started out as a comment, but...) To add some more data to marc_s's answer, datetime will also fail with yyyy-mm-dd HH:MM:ss - So

SET LANGUAGE GERMAN;
DECLARE @dateTimeFailes DATETIME ='2017-01-13 00:00:00';

Will result with the same error. However, Once you replace the space between the Date and Time parts with a T, SQL Server will suddenly understand the format -

SET LANGUAGE GERMAN;
DECLARE @dateTime DATETIME ='2017-01-13T00:00:00';

Will work as expected.

Here is a quick list of acceptable and unacceptable formats:

SET LANGUAGE GERMAN;
-- Correct 
DECLARE @date DATE ='2017-01-13';
DECLARE @dateTime DATETIME ='2017-01-13T00:00:00'; 
DECLARE @dateTimeDateOnly DATETIME ='20170113';

-- Incorrect
DECLARE @WrongFormatDateTime DATETIME ='2017-01-13 00:00:00'; 
DECLARE @WrongFormatDate DATETIME ='2017-01-13';