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

適用於: 是SQL Server 是Azure SQL Database 是Azure SQL 資料倉儲 是平行處理資料倉儲 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. 這些字串的類型為 datesmalldatetimedatetimedatetime2datetimeoffsetThese 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

datedatetime2datetimeoffset 資料類型不支援 DATEFORMAT ydmThe 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. 例如,datetimesmalldatetime 解譯可能不符合 datedatetime2datetimeoffsetFor 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

需要 public 角色的成員資格。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)