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
, aliteral varchar
or avariable 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