主页 / dba / 问题 / 166771
Accepted
Aaron Bertrand
Asked: 2017-03-10 18:47:32 +0800 CST

LANGUAGE 和 DATEFORMAT 安全的日期/时间文字格式是什么？

  • 772

由于 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, Level 16, 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);

结果：

消息 242，级别 16，状态 3
将 varchar 数据类型转换为 datetime 数据类型导致值超出范围。

（这不是 Microsoft Access，它对您来说“很好”并为您修复了转置。此外，在某些情况下可能会发生类似的错误SET DATEFORMAT ydm;- 这不仅仅是语言问题，这只是更常见的情况，其中这些破损发生 - 并不总是被注意到，因为有时它们不是错误，只是 8 月 7 日变成了 7 月 8 日，没有人注意到。）

所以，问题：

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

sql-server t-sql

1 个回答

  1. Best Answer

    在文档中，非常明确地指出，唯一安全的格式是我在问题开始时演示的格式：

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

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

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

    TL；DR：这是真的。对于datetime和smalldatetime。

    继续阅读更长的版本，以及尽可能多的证据。

    有一个漏洞可以解释这一点 - 虽然正文未能承认yyyyMMdd hh:...是一种不受转置语言或日期格式解释的安全格式，但有一点模糊说这种字符串的日期部分未根据 dateformat 设置进行验证：

    在此处输入图像描述

    通常情况下，我只是按原样接受文档，这与我不同。你可以说我有点怀疑。而且这里的语言也是模棱两可的——它只是说明这是关于日期和时间的组合，而不是明确地指出空格（据我所知，这可能是一个回车）。它还说它不是多语言的，这意味着它可能会在某些语言中失败，但我们很快就会发现这也是不正确的。

    所以我开始证明语言/日期格式的组合不会使这种特定格式失败。

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

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

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

    EXEC sys.sp_executesql @sql, N'@lang sysname', N'us_english';
EXEC sys.sp_executesql @sql, N'@lang sysname', N'Deutsch';
EXEC sys.sp_executesql @sql, N'@lang sysname', N'Français';
EXEC sys.sp_executesql @sql, N'@lang sysname', N'日本語';
...
EXEC sys.sp_executesql @sql, N'@lang sysname', N'简体中文';
EXEC sys.sp_executesql @sql, N'@lang sysname', N'Arabic';
EXEC sys.sp_executesql @sql, N'@lang sysname', N'ไทย';
EXEC sys.sp_executesql @sql, N'@lang sysname', 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

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

    消息 241，级别 16，状态 1，第 8 行
    从字符串转换日期和/或时间时转换失败。

    为完整起见，还有第四种安全格式，但它仅对转换为较新的日期/时间类型 ( 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), DATENAME(MONTH,@dt2);

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

    März    Juli    März

    yyyyMMdd适用于 datetime ( )的格式也将始终适用于 date 和其他新类型。所以，恕我直言，总是使用它。并且给定带有日期/时间 ( yyyyMMdd hh:...) 的类型的第三种格式，这实际上会让您更加一致 - 即使日期组件总是不太可读。

    现在，当我谈论日期的字符串表示时，我只需要几年时间，就可以养成演示三种安全格式的习惯。

    • 31

相关问题