sql-server – 什么日期/时间文字格式是LANGUAGE和DATEFORMAT安

很容易证明,由于SET LANGUAGE,SET DATEFORMAT或登录的默认语言,除了以下两种格式之外的许多日期/时间格式都容易被误解：

yyyyMMdd                 -- unseparated,date only
yyyy-MM-ddThh:mm:ss.fff  -- date dash separated,date/time separated by T

即使是没有T的这种格式也可能看起来像有效的ISO 8601格式,但它在几种语言中失败：

DECLARE @d varchar(32) = '2017-03-13 23:22:21.020';

SET LANGUAGE Deutsch;
SELECT CONVERT(datetime,@d);

SET LANGUAGE Fran?ais;
SELECT CONVERT(datetime,@d);

结果：

Die Spracheneinstellung wurde auf Deutsch ge?ndert.

Msg 242,Level 16,State 3

Bei der Konvertierung eines varchar-Datentyps in einen datetime-Datentyp liegt der Wert au?erhalb des gültigen Bereichs.

Le paramètre de langue est passé à Fran?ais.

Msg 242,State 3

La conversion d’un type de données varchar en type de données datetime a créé une valeur hors limites.

现在,这些失败就像在英语中我已经转换了月份和日期一样,以制定yyyy-dd-mm的日期组件：

DECLARE @d varchar(32) = '2017-13-03 23:22:21.020';

SET LANGUAGE us_english;
SELECT CONVERT(datetime,@d);

结果：

Msg 242,State 3

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

(这不是Microsoft Access,这对你来说很“好”,并为你修复了转置.而且,在某些情况下,使用SET DATEFORMAT ydm会发生类似的错误; – 它不仅仅是一种语言,它只是更多这些破损发生的常见情况 – 并不总是被注意到,因为有时它们不是错误,只是8月7日变为7月8日而没有人注意到.)

那么,问题是：

既然我知道有一堆不安全的格式,那么在给定任何语言和日期格式组合的情况下,是否还有其他格式是安全的？

解决方法

yyyyMMdd                 -- unseparated,date/time separated by T

但是,最近我注意到第三种格式同样不受任何语言或日期格式设置的影响：

yyyyMMdd hh:mm:ss.fff    -- unseparated date,no T separator

TL; DR：这是事实.对于datetime和smalldatetime.

继续阅读更长的版本,并获得尽可能多的证据.

有一个漏洞解释了这个 – 虽然主要的文本主体没有承认yyyyMMdd ……：作为一种安全的转换语言或日期格式解释的格式,有一点模糊说明这样的字符串的日期部分不是根据dateformat设置验证：

07001

通常情况下,只需要记录文档即可.你可以说我有点怀疑.这里的语言也含糊不清 – 它只是声明这是关于日期和时间的组合,而不是明确地调用空格(可能是回车,对于我所知道的所有).它还说它不是多语言,这意味着它可能在某些语言中失败,但我们很快就会发现这也是不正确的.

所以我开始证明没有语言/日期格式的组合可以使这种特定的格式失败.

首先,我为每种语言创建了一小块动态SQL：

EXEC sys.sp_executesql @sql,N'@lang sysname',N'us_english';

这产生了34行输出,如下所示：

EXEC sys.sp_executesql @sql,N'us_english';
EXEC sys.sp_executesql @sql,N'Deutsch';
EXEC sys.sp_executesql @sql,N'Fran?ais';
EXEC sys.sp_executesql @sql,N'日本語';
...
EXEC sys.sp_executesql @sql,N'简体中文';
EXEC sys.sp_executesql @sql,N'Arabic';
EXEC sys.sp_executesql @sql,N'???';
EXEC sys.sp_executesql @sql,N'norsk (bokm?l)';

我将该输出复制到一个新的查询窗口,在它上面,我生成了这个代码,希望在至少一个案例中尝试将同一日期(3月13日)转换为第13个月的第3天：

DECLARE @sql nvarchar(max) = N'
SET LANGUAGE @lang;
SET DATEFORMAT ydm;
SELECT @@LANGUAGE,CONVERT(datetime,''20170313 23:22:21.020'');';

不,每种语言都可以在ydm找到.我也尝试了所有其他格式,以及每个日期/时间数据类型.每次都有34次成功转换到3月13日.

所以,我确实向@AndriyM和@ErikE承认,确实存在第三种安全格式.我将在未来的帖子中记住这一点,但是我已经在很多地方敲响了关于其他两个人的鼓声,我现在不打算将它们全部追捕并纠正它们.

通过扩展,你会认为这个是安全的,但没有：

yyyyMMddThh:mm:ss.fff    -- unseparated date,T separator

我认为在每种语言中,这将产生相当于：

Msg 241,State 1,Line 8

Conversion failed when converting date and/or time from character string.

为了完整起见,有第四种安全格式,但只有转换为较新的日期/时间类型(date,datetime2,datetimeoffset)才是安全的.在这些情况下,语言设置不会干扰：

yyyy-MM-dd hh:mm:...

但是,我强烈建议不要使用它,因为它只适用于较新的类型,而且根据我的经验,旧的类型仍然大量使用.为什么在其他地方(或实际上在相同的代码中,如果数据类型发生变化)有破折号你必须删除它们？

SET LANGUAGE Deutsch;
DECLARE @dashes char(10) = '2017-03-07 03:34';
DECLARE @d date = @dashes,@dt datetime = @dashes,@dt2 datetime2 = @dashes;

SELECT DATENAME(MONTH,@d),DATENAME(MONTH,@dt),@dt2);

即使给出相同的源字符串,转换也会产生完全不同的结果：

M?rz    Juli    M?rz

适用于datetime的格式(yyyyMMdd)也将始终适用于日期和其他新类型.所以,恕我直言,只是总是使用它.并且给定了具有日期/时间的类型的第三种格式(yyyyMMdd hh：…),这实际上允许您更加一致 – 即使日期组件总是稍微不那么可读.

现在,当我谈论日期的字符串表示时,我将花费几年时间,给予或接受,养成演示三种安全格式的习惯.

（编辑：PHP编程网 - 黄冈站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!