SET DATEFORMAT (TSQL Statement)

Sets the current DATEFORMAT setting.

Syntax

SET DATEFORMAT dateformat

Arguments

dateformat: must be MDY, DMY or YMD. It is an identifier, a literal varchar or a variable of type varchar.

Remarks

The DATEFORMAT setting specifies the order of date parts in a string.

DATEFORMAT Value	Order of Date Parts
DMY	Day-Month-Year
MDY	Month-Day-Year
YMD	Year-Month-Day

The statement SET DATEFORMAT overrides the default DATEFORMAT value of the current language.

The current setting for DATEFORMAT affects the functions CAST and CONVERT, for conversion from varchar to datetime.

The value of this settings specifies the order of the day, month and year parts in the string.
If the string to convert begins with 4-digits year, the order YMD is assumed.

This setting can be modified with SET LANGUAGE or SET DATEFORMAT.

The function @@DATEFORMAT returns the current DATEFORMAT setting.

MS SQL Server doesn’t implement the function @@DATEFORMAT.

Examples

DECLARE @s VARCHAR(20) = '02-01-2014';  -- If MDY: February 1st. If DMY: January 2nd
DECLARE @u VARCHAR(20) = '2014-02-01';  -- always February 1st

SET LANGUAGE en_US;
PRINT 'Current language changed to ' + @@LANGUAGE + ', @@DATEFORMAT is ' + @@DATEFORMAT;
PRINT CAST(@s AS DATE);
PRINT CAST(@u AS DATE);

PRINT '';
SET LANGUAGE fr_FR;
PRINT 'Current language changed to ' + @@LANGUAGE + ', @@DATEFORMAT is ' + @@DATEFORMAT;
PRINT CAST(@s AS DATE);
PRINT CAST(@u AS DATE);

PRINT '';
SET DATEFORMAT MDY;
PRINT 'Change DATEFORMAT to MDY';
PRINT 'Current language is ' + @@LANGUAGE + ', @@DATEFORMAT is ' + @@DATEFORMAT;
PRINT CAST(@s AS DATE);
PRINT CAST(@u AS DATE);

PRINT '';
SET LANGUAGE fr_FR;
PRINT 'Current language changed to ' + @@LANGUAGE + ', @@DATEFORMAT is ' + @@DATEFORMAT;
PRINT CAST(@s AS DATE);
PRINT CAST(@u AS DATE);

The result is:

Current language changed to en-US, @@DATEFORMAT is MDY
2014-02-01
2014-02-01

Current language changed to fr-FR, @@DATEFORMAT is DMY
2014-01-02       <--- French parses @s as January 2nd
2014-02-01

Change DATEFORMAT to MDY
Current language is fr-FR, @@DATEFORMAT is MDY
2014-02-01
2014-02-01

Current language changed to fr-FR, @@DATEFORMAT is DMY
2014-01-02       <--- French parses @s as January 2nd
2014-02-01

Guide