SQL Server如何决定隐式datetime转换的格式？ [英] How does SQL Server decide format for implicit datetime conversion?

问题描述

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
数据类型转换为datetime数据类型超出范围的值。

Msg 242, Level 16, State 3, Line 4
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

我的问题是SQL Server如何决定使用哪种格式进行隐式datetime转换？ p>

My question is how SQL Server decides which format to use for implicit datetime conversion?

推荐答案

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

This can depend on a variety of factors - the operating system's regional settings, the current user's language and dateformat settings. By default, Windows uses US English, and the user's settings are US English and MDY.

但是，这里有一些示例来显示如何更改。

But here are some examples to show how this can change.

用户正在使用BRITISH语言设置： / p>

User is using BRITISH language settings:

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

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

（错误）

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

Msg 242, Level 16, State 3, Line 5
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

用户正在使用Français：

User is using Français:

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

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

（错误）

消息242，第16级，状态3，第1行
La conversion d'un type de
donnéesvarchar en type dedonnéesdatetime acrééune valeur hors
限制。

Msg 242, Level 16, State 3, Line 1
La conversion d'un type de données varchar en type de données datetime a créé une valeur hors limites.

用户再次使用Français：

User is again using Français:

SET LANGUAGE FRENCH;

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

（错误）

消息242，级别16，状态3，第1行
La conversion d'un type dedonnées
varchar en type dedonnéesdatetime acrééune valeur hors limites。 p>

Msg 242, Level 16, State 3, Line 1
La conversion d'un type de données varchar en type de données datetime a créé une valeur hors limites.

用户正在使用DMY而不是MDY：

User is using DMY instead of 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

（错误）

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

Msg 242, Level 16, State 3, Line 2
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

最好的选择是使用ISO标准，非区域，安全，无歧义的日期格式。我通常推荐的是：

Your best bet, always, is to use ISO standard, non-regional, safe, unambiguous date formats. The two I typically recommend are:

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的字符串格式。

Therefore, I strongly recommend that instead of letting users type in free text date formats (or that you use unreliable formats yourself), control your input strings and make sure they adhere to one of these safe formats. Then it won't matter what settings the user has or what the underlying regional settings are, your dates will always be interpreted as the dates they were intended to be. If you are currently letting users enter dates into a text field on a form, stop doing that and implement a calendar control or at least a pick list so you can ultimately control the string format that is passed back to SQL Server.

对于某些背景，请阅读Tibor Karaszi的终极指南到datetime数据类型和我的帖子坏习惯踢：错误处理日期/范围查询

For some background, please read Tibor Karaszi's "The ultimate guide to the datetime datatypes" and my post "Bad Habits to Kick : Mis-handling date / range queries."

这篇关于SQL Server如何决定隐式datetime转换的格式？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！