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 ValueOrder of Date Parts
DMYDay-Month-Year
MDYMonth-Day-Year
YMDYear-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.

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