日期转换和区域性：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屋！