SET DATEFORMAT (Transact-SQL)

Article
05/23/2023

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 }

Note

To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation.

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