日期转换和文化：DATE与DATETIME的区别

Question

我写过很多关于字符串转换为date或datetime的答案。作为生活在德语国家的人，我习惯于处理非美式英语日期格式，并且我习惯使用安全文字（我更喜欢ODBC格式），我从不使用没有第三个参数的CONVERT。这不是问题，请不要在这个方向上提供答案...

经常可以看到，格式yyyy-mm-dd是标准的（ISO8601、ANSI等），因此与文化无关。

今天我不得不编辑其中一个旧答案，因为我曾经说过，观察到的行为取决于其他事情。

问题是：

为什么（如果有原因）DATE和DATETIME之间存在差异？

...至少在我的环境中，SQL Server 2014（12.0.4237.0）目前是如此。

我希望这以前没有被问过...

试试这个：

没有问题，在这里DATE按预期工作

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

现在用 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'); 

这是一个bug、故意的还是无心之失？

- Shnugo

Answer 1

< p > DATETIME（旧类型）的ISO-8601在某种程度上被“破坏”或“适应”（取决于您是将其视为错误还是特征），您需要使用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) - 更易于使用！） :-)

- marc_s

---

正如我所预料的那样，这似乎是历史遗留问题。你的陈述“只需注册它，了解它 - 然后继续前进”（意思是：不要再使用DATETIME - 而是使用DATE或DATETIME2（n））是令人信服的 :-D - Shnugo

@Shnugo：是的，这很可能是一个遗留问题 - 我们甚至可以归咎于Sybase最初的错误 :-D - marc_s

@LukStorms: 是的，有问题！我得到了Msg 241，Level 16，State 1，Line 3 Fehler beim Konvertieren einer Zeichenfolge in ein Datum und/oder eine Uhrzeit.这基本上意味着：在转换日期和/或时间戳时出错。没有中间的“T”格式对于英语以外的语言不起作用-请参见Zohar下面的出色回答。 - marc_s

@LukStorms - 你没有因为日期的问题而出错 - 你的日期部分是12。在德语环境下，SQL Server会将其转换为2017年12月1日 - 尝试SET LANGUAGE GERMAN; SELECT DATENAME(MONTH, CONVERT(DateTime, '2017-01-12 00:00:00')); 结果为“Dezember”。如果你的日期部分是13，你将会得到一个错误信息 - “Bei der Konvertierung eines varchar-Datentyps in einen datetime-Datentyp liegt der Wert außerhalb des gültigen Bereichs。” - 我假设这个错误信息在德语版本中也是如此。 - Zohar Peled

@LukStorms - 是的，ODBC规范的日期格式（120、121）并不是ISO8601（126）。我猜Marc应该编辑他的回答来修正这个错误。 - Zohar Peled

Answer 2

（起初只是一条评论，但是……）为了给 marc_s 的回答增加更多数据，datetime 在使用 yyyy-mm-dd HH:MM:ss 格式时也会失败 - 因此。

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

- Zohar Peled

---

完全正确 - 这是第二个 ISO-8601 格式，适用于 DATETIME，无论语言和区域设置如何 - 感谢您的添加。再次强调：'2017-01-13 00:00:00' 将与 DATETIME2(n) 完美地配合使用 - 这是抛弃 DATETIME 的又一个理由.... - marc_s

@marc_s 谢谢！我刚想起几年前读过一篇博客文章 - 你可能想把这个链接加到你的答案里：为什么你不应该再使用datetime - Zohar Peled