• Article

SET DATEFORMAT (Transact-SQL)

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 Functions (Transact-SQL). For information and examples that are common to date and time data types and functions, see Using Date and Time Data.

Topic link iconTransact-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 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. For more information about how to use DATEFORMAT with different formats see the "String Literal Date and Time Formats" section in Using Date and Time Data.

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