SQL Server 如何决定隐式日期时间转换的格式？

declare @str_datetime varchar(50)
set @str_datetime='30-04-2012 19:01:45' -- 30th April 2012
declare @dt_datetime datetime
select @dt_datetime=@str_datetime

这给出了以下错误：

消息 242，第 16 级，状态 3，第 4 行
varchar 的转换 数据类型转换为日期时间数据类型导致值超出范围。

我的问题是 SQL Server 如何决定使用哪种格式进行隐式日期时间转换？

这可能取决于多种因素 - 操作系统的区域设置、当前用户的语言和日期格式设置。默认情况下，Windows 使用US English，用户的设置是US English and MDY.

但这里有一些例子来展示这种情况如何改变。

用户正在使用英国语言​​设置：

-- works:
SET LANGUAGE BRITISH;
SELECT CONVERT(DATETIME, '30-04-2012 19:01:45');

-- fails:
SELECT CONVERT(DATETIME, '04/13/2012');
GO

(Error)

消息 242，第 16 级，状态 3，第 5 行
varchar数据的转换 类型转换为日期时间数据类型导致值超出范围。

用户正在使用 Français：

-- works:
SET LANGUAGE FRENCH;
SELECT CONVERT(DATETIME, '30-04-2012 19:01:45');

-- fails:
SELECT CONVERT(DATETIME, '04/13/2012');
GO

(Error)

消息 242，第 16 级，状态 3，第 1 行
类型转换 données varchar en type de données datetime a créé une valeur hors 限制。

用户再次使用法语：

SET LANGUAGE FRENCH;

-- fails (proving that, contrary to popular belief, YYYY-MM-DD is not always safe):
SELECT CONVERT(DATETIME, '2012-04-30');
GO

(Error)

消息 242，第 16 级，状态 3，第 1 行
唐尼类型的转换 varchar 是一种日期时间类型，它的值不受限制。

用户使用 DMY 而不是 MDY：

SET LANGUAGE ENGLISH;
SET DATEFORMAT DMY;

-- works:
SELECT CONVERT(DATETIME, '30-04-2012 19:01:45');

-- fails:
SELECT CONVERT(DATETIME, '04-30-2012');
GO

(Error)

消息 242，第 16 级，状态 3，第 2 行
varchar 的转换 数据类型转换为日期时间数据类型导致值超出范围。

最好的选择始终是使用 ISO 标准、非区域性、安全、明确的日期格式。我通常推荐的两个是：

YYYYMMDD                  - for date only.
YYYY-MM-DDTHH:MM:SS[.mmm] - for date + time, and yes that T is important.

这些都没有失败：

SET DATEFORMAT MDY;
SET LANGUAGE ENGLISH;
SELECT CONVERT(DATETIME, '20120430');
SELECT CONVERT(DATETIME, '2012-04-30T19:01:45');
SET LANGUAGE FRENCH;
SELECT CONVERT(DATETIME, '20120430');
SELECT CONVERT(DATETIME, '2012-04-30T19:01:45');
SET LANGUAGE BRITISH;
SELECT CONVERT(DATETIME, '20120430');
SELECT CONVERT(DATETIME, '2012-04-30T19:01:45');
SET DATEFORMAT DMY;
SELECT CONVERT(DATETIME, '20120430');
SELECT CONVERT(DATETIME, '2012-04-30T19:01:45');

因此，我强烈建议不要让用户输入自由文本日期格式（或者您自己使用不可靠的格式），而是控制您的输入字符串并确保它们遵循这些安全格式之一。这样，无论用户拥有什么设置或底层区域设置是什么，您的日期将始终被解释为预期的日期。如果您当前允许用户在表单上的文本字段中输入日期，请​​停止这样做并实现一个日历控件或至少一个选择列表，以便您最终可以控制传回 SQL Server 的字符串格式。

有关一些背景信息，请阅读：

• 蒂博尔·卡拉齐的日期时间数据类型的终极指南 https://karaszi.com/the-ultimate-guide-to-the-datetime-datatypes
• 中的所有链接负责任地约会 https://sqlblog.org/dates