日期转换和文化:DATE 和 DATETIME 之间的区别 [英] Date conversion and culture: Difference between DATE and 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!) :-)

这篇关于日期转换和文化:DATE 和 DATETIME 之间的区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！