SET DATEFORMAT (Transact-SQL)SET DATEFORMAT (Transact-SQL)

适用对象: yesSQL ServeryesAzure SQL 数据库yesAzure SQL 数据仓库yes并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

设置用于解释日期字符串的月、日和年日期部分的顺序。Sets the order of the month, day, and year date parts for interpreting date character strings. 这些字符串属于类型“date”、“smalldatetime”、“datetime”、“datetime2”或“datetimeoffset”。These strings are of type date, smalldatetime, datetime, datetime2, or datetimeoffset.

有关所有 Transact-SQLTransact-SQL 日期和时间数据类型及函数的概述,请参阅日期和时间数据类型及函数 (Transact-SQL)For an overview of all Transact-SQLTransact-SQL date and time data types and functions, see Date and Time Data Types and Functions (Transact-SQL).

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

SET DATEFORMAT { format | @format_var }   

参数Arguments

format | @format_varformat | @format_var
日期部分的顺序。Is the order of the date parts. 有效参数为 mdydmyymdydmmyddymValid parameters are mdy, dmy, ymd, ydm, myd, and dym. 可以是 Unicode,也可以是转换为 Unicode 的双字节字符集 (DBCS)。Can be either Unicode or double-byte character sets (DBCS) converted to Unicode. 美国英语默认值为 mdyThe U.S. English default is mdy. 有关所有支持语言的默认 DATEFORMAT,请参阅 sp_helplanguage (Transact-SQL)For the default DATEFORMAT of all support languages, see sp_helplanguage (Transact-SQL).

RemarksRemarks

“date”、“datetime2”和“datetimeoffset”数据类型不支持 DATEFORMAT“ydm”。The DATEFORMAT ydm isn't supported for date, datetime2, and datetimeoffset data types.

DATEFORMAT 设置可能会对日期数据类型的字符串进行不同的解释,具体取决于字符串格式。The DATEFORMAT setting may interpret character strings differently for date data types, depending on their string format. 例如,“datetime”和“smalldatetime”的解释可能与“date”、“datetime2”或“datetimeoffset”不一致。For example, datetime and smalldatetime interpretations may not match date, datetime2, or datetimeoffset. 在字符串转换为数据库的日期值时,DATEFORMAT 会影响字符串的解释。DATEFORMAT affects the interpretation of character strings as they're converted to date values for the database. 它不会影响日期数据类型值的显示,也不会影响数据库中的存储格式。It doesn't affect the display of date data type values, nor their storage format in the database.

某些字符串格式(例如 ISO 8601)的解释与 DATEFORMAT 设置无关。Some character string formats, for example ISO 8601, are interpreted independently of the DATEFORMAT setting.

SET DATEFORMAT 的设置是在执行或运行时设置,而不是在分析时设置。The setting of SET DATEFORMAT is set at execute or run time and not at parse time.

SET DATEFORMAT 将覆盖 SET LANGUAGE 的隐式日期格式设置。SET DATEFORMAT overrides the implicit date format setting of SET LANGUAGE.

权限Permissions

要求 公共 角色具有成员身份。Requires membership in the public role.

示例Examples

下例使用不同的日期字符串作为具有相同 DATEFORMAT 设置的会话中的输入。The following example uses different date strings as inputs in sessions with the same DATEFORMAT setting.

-- Set date format to day/month/year.  
SET DATEFORMAT dmy;  
GO  
DECLARE @datevar datetime2 = '31/12/2008 09:01:01.1234567';  
SELECT @datevar;  
GO  
-- Result: 2008-12-31 09:01:01.123  
SET DATEFORMAT dmy;  
GO  
DECLARE @datevar datetime2 = '12/31/2008 09:01:01.1234567';  
SELECT @datevar;  
GO  
-- Result: Msg 241: Conversion failed when converting date and/or time -- from character string.  
  
GO  

另请参阅See Also

SET 语句 (Transact-SQL)SET Statements (Transact-SQL)