Date Conversion and Culture: The Difference Between DATE and DATETIME

Question

I wrote a lot of answers about conversions dateor datetimefrom strings. Living in a German-speaking country, I'm used to dealing with date formats other than us_english, and I'm used to using protected literals (I prefer the format ODBC), and I never use CONVERTwithout the third parameter. This is not a question, and please do not give answers in this direction ...

It can often be read that the format yyyy-mm-ddis standard (ISO8601, ANSI, independently) and therefore culture independent.

Today I had to edit one of these older answers , as I said there that the observed behavior depends on something else.

The question arises:

Why (if there is a reason) is there a difference between dateand datetime?

... at least in my environment, which is currently SQL Server 2014 (12.0.4237.0).

I hope this has not been asked before ...

Try the following:

There are no problems, it dateworks 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 it a mistake, a goal or just grubbiness?

Answer 1

ISO-8601 DATETIME ( ) - "" "" ( , ) - YYYYMMDD ( ), .

DATE DATETIME2(n) , ISO-8601 YYYY-MM-DD .

-- 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'); 

DATETIME ( ...) - , - (: DATETIME) - DATE DATETIME2(n) - !): -)

Answer 2

( , ...) marc_s, datetime yyyy-mm-dd HH:MM:ss - So

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

. , T, SQL Server -

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

.

:

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';