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

前端之家收集整理的这篇文章主要介绍了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日而没有人注意到.)

那么,问题是:

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

解决方法

the documentation中,非常明确地指出,唯一安全的格式是我在问题的最开始时所展示的格式:
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:…),这实际上允许您更加一致 – 即使日期组件总是稍微不那么可读.

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

原文链接:https://www.f2er.com/mssql/79581.html

猜你在找的MsSQL相关文章