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：…),这实际上允许您更加一致 – 即使日期组件总是稍微不那么可读.
现在,当我谈论日期的字符串表示时,我将花费几年时间,给予或接受,养成演示三种安全格式的习惯.
