SET DATEFIRST (TSQL Statement)

Sets the current DATEFIRST setting.

Syntax

SET DATEFIRST datefirst

Arguments

datefirst

must be an integer in the range 1 to 7, of type int. It is a literal int or a variable of type int.

Remarks

The DATEFIRST setting specifies the first day of the week.

DATEFIRST Value	Day
1	Monday
2	Tuesday
3	Wednesday
4	Thursday
5	Friday
6	Saturday
7	Sunday

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

The result returned by some functions, e.g. DATEPART and DATENAME, depends on the current setting for the first day of week.

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

The function @@DATEFIRST returns the current DATEFIRST setting.

Examples

DECLARE @d DATETIME = '20140201'; -- is Saturday

SET LANGUAGE en_US;
PRINT 'Current language changed to ' + @@LANGUAGE + ', @@DATEFIRST is ' + CAST(@@DATEFIRST AS VARCHAR);
PRINT DATEPART(dw, @d)

PRINT '';
SET LANGUAGE fr_FR;
PRINT 'Current language changed to ' + @@LANGUAGE + ', @@DATEFIRST is ' + CAST(@@DATEFIRST AS VARCHAR);
PRINT DATEPART(dw, @d)

PRINT '';
SET DATEFIRST 7;
PRINT 'Change DATEFIRST to 7';
PRINT 'Current language id ' + @@LANGUAGE + ', @@DATEFIRST is ' + CAST(@@DATEFIRST AS VARCHAR);
PRINT DATEPART(dw, @d)

PRINT '';
SET LANGUAGE fr_FR;
PRINT 'Current language changed to ' + @@LANGUAGE + ', @@DATEFIRST is ' + CAST(@@DATEFIRST AS VARCHAR);
PRINT DATEPART(dw, @d)

The result is:

Current language changed to en-US, @@DATEFIRST is 7
7

Current language changed to fr-FR, @@DATEFIRST is 1
6

Change DATEFIRST to 7
Current language id fr-FR, @@DATEFIRST is 7
7

Current language changed to fr-FR, @@DATEFIRST is 1
6