SET DATEFORMAT (Transact-SQL)
Applies to: 
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
SQL analytics endpoint in Microsoft Fabric
Warehouse in Microsoft Fabric
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 Transact-SQL date and time data types and functions, see Date and Time Data Types and Functions (Transact-SQL).
Transact-SQL syntax conventions
Syntax
SET DATEFORMAT { format | @format_var }
Arguments
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).
Remarks
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.
Permissions
Requires membership in the public role.
Examples
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