SET DATEFORMAT (Transact-SQL)

SET DATEFORMAT (Transact-SQL)

 

THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

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).

Topic link icon Transact-SQL Syntax Conventions

  
SET DATEFORMAT { format | @format_var }   

-- Azure SQL Data Warehouse and Parallel Data Warehouse  
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  

-- Set date format to month/day/year.  
SET DATEFORMAT mdy;  
DECLARE @datevar datetime2 = '12/31/2012 09:01:01.1234567';  
SELECT @datevar;  
  

SET Statements (Transact-SQL)

Community Additions

ADD
Show:
© 2016 Microsoft