Transact-SQL Syntax ConventionsSets 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).
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 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