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 ValueDay
1Monday
2Tuesday
3Wednesday
4Thursday
5Friday
6Saturday
7Sunday

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