愿所有的美好和期待都能如约而至

日期转换和文化:DATE 和 DATETIME 之间的区别

发布时间:  来源:互联网  作者:匿名  标签:casting date datetime error Date conversion and culture: Difference between DATE  热度:37.5℃

本文介绍了日期转换和文化:DATE 和 DATETIME 之间的区别的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我写了很多关于从字符串转换 datedatetime 的答案.生活在德语国家,我习惯于处理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.

对于 DATEDATETIME2(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 不再 – 使用 DATEDATETIME2(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!)

勇敢去编程!

勇敢的热爱编程,未来的你一定会大放异彩,未来的生活一定会因编程更好!

TOP