| title | SET DATEFORMAT (Transact-SQL) | ||||||||
|---|---|---|---|---|---|---|---|---|---|
| description | SET DATEFORMAT (Transact-SQL) | ||||||||
| author | WilliamDAssafMSFT | ||||||||
| ms.author | wiassaf | ||||||||
| ms.date | 03/14/2017 | ||||||||
| ms.service | sql | ||||||||
| ms.subservice | t-sql | ||||||||
| ms.topic | reference | ||||||||
| ms.custom |
|
||||||||
| f1_keywords |
|
||||||||
| helpviewer_keywords |
|
||||||||
| dev_langs |
|
||||||||
| monikerRange | >=aps-pdw-2016 || =azuresqldb-current || =azure-sqldw-latest || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric || =fabric-sqldb |
[!INCLUDE sql-asdb-asdbmi-asa-pdw-fabricse-fabricdw-fabricsqldb]
Sets the order of the month, day, and year date parts for interpreting date character strings. These strings are of type date, smalldatetime, datetime, datetime2, or datetimeoffset.
For an overview of all [!INCLUDEtsql] date and time data types and functions, see Date and Time Data Types and Functions (Transact-SQL).
:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions
SET DATEFORMAT { format | @format_var }
format | @format_var
Is the order of the date parts. Valid parameters are mdy, dmy, ymd, ydm, myd, and dym. Can be either Unicode or double-byte character sets (DBCS) converted to Unicode. The U.S. English default is mdy. For the default DATEFORMAT of all support languages, see sp_helplanguage (Transact-SQL).
The DATEFORMAT ydm isn't supported for date, datetime2, and datetimeoffset data types.
The DATEFORMAT setting may interpret character strings differently for date data types, depending on their string format. For example, datetime and smalldatetime interpretations may not match date, datetime2, or datetimeoffset. 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.
Some character string formats, for example ISO 8601, are interpreted independently of the DATEFORMAT setting.
The setting of SET DATEFORMAT is set at execute or run time and not at parse time.
SET DATEFORMAT overrides the implicit date format setting of SET LANGUAGE.
Requires membership in the public role.
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