日期转换和文化:DATE 和 DATETIME 之间的区别
我写了很多关于从字符串转换 date 或 datetime 的答案.生活在德语国家，我习惯于处理non-us_english 日期格式，我习惯于使用安全文字(我更喜欢 ODBC 格式)和如果没有第三个参数，我从不使用 CONVERT.这不是问题，请不要在这个方向上提供答案...
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...
人们经常会读到，yyyy-mm-dd 格式是标准格式(ISO8601、ANSI 等等)，因此与文化无关.
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.
问题是:
...至少在我的环境中，目前是 SQL Server 2014 (12.0.4237.0).
... at least in my environmen, which is SQL Server 2014 (12.0.4237.0) at the moment.
我希望，这之前没有问过...
I hope, this was not asked before...
试试这个:
这里没有问题，DATE 按预期工作
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');
但现在用 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?
推荐答案
DATETIME(旧类型)的 ISO-8601 不知何故被破坏"或适应"(取决于你是否看将其视为错误或功能)-您需要使用 YYYYMMDD(没有 任何破折号)使其工作，而不管语言设置如何.
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.
对于 DATE 或 DATETIME2(n) 数据类型，这已得到修复，并且正确"的 ISO-8601 格式 YYYY-MM-DD 将始终被正确解释.
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');
这是 DATETIME 类型的一个怪癖(而不是唯一的....) - 只需注册它，了解它 - 然后继续(意思是:不要使用 DATETIME 不再 - 使用 DATE 或 DATETIME2(n) 代替 - 更好用！):-)
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!) :-)
相关文章