SET DATEFORMAT (Transact-SQL)
Applies To: SQL Server 2014, SQL Server 2016 Preview
Sets the order of the month, day, and year date parts for interpreting date, smalldatetime, datetime, datetime2 and datetimeoffset character strings.
For an overview of all Transact-SQL date and time data types and functions, see Date and Time Data Types and Functions (Transact-SQL).
Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database, Azure SQL Data Warehouse Public Preview.
- 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 is not supported for date, datetime2 and datetimeoffset data types.
The effect of the DATEFORMAT setting on the interpretation of character strings might be different for datetime and smalldatetime values than for date, datetime2 and datetimeoffset values, depending on the string format. This setting affects the interpretation of character strings as they are converted to date values for storage in the database. It does not affect the display of date data type values that are stored in the database or the storage format.
Some character strings 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