Export (0) Print
Expand All
Expand Minimize


Applies To: SQL Server 2014, SQL Server 2016 Preview

Topic Status: Some information in this topic is preview and subject to change in future releases. Preview information describes new features or changes to existing features in Microsoft SQL Server 2016 Community Technology Preview 2 (CTP2).

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.

Topic link icon 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.
DECLARE @datevar datetime2 = '31/12/2008 09:01:01.1234567';
SELECT @datevar;
-- Result: 2008-12-31 09:01:01.123
DECLARE @datevar datetime2 = '12/31/2008 09:01:01.1234567';
SELECT @datevar;
-- Result: Msg 241: Conversion failed when converting date and/or time -- from character string.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

© 2015 Microsoft