Interview Question of the Week #046 – How @@DATEFIRST and SET DATEFIRST Are Related?

Question: How are SET DATEFIRST and @@DATEFIRST related? SET DATEFIRST chooses the session’s first weekday, from 1 for Monday through 7 for Sunday. @@DATEFIRST reads the session’s current choice.

When our company started serving clients in different countries, I was glad to learn this small setting. A report’s weekday numbering can be confusing when two connections use different language defaults.

-- Use a private test session: SET LANGUAGE also changes date parsing defaults.
DECLARE @OldLanguage sysname=@@LANGUAGE,@OldDateFirst int=@@DATEFIRST;
SET LANGUAGE Italian;
SELECT @@LANGUAGE AS SessionLanguage,@@DATEFIRST AS FirstDay;
SET LANGUAGE us_english;
SELECT @@LANGUAGE AS SessionLanguage,@@DATEFIRST AS FirstDay;
SET DATEFIRST 5;
SELECT @@DATEFIRST AS FirstDay,
       DATEPART(weekday,CONVERT(date,'20151120',112)) AS FridayNumber;
SET LANGUAGE @OldLanguage;
SET DATEFIRST @OldDateFirst;

The Italian default reports Monday, 1. US English reports Sunday, 7. SET DATEFIRST 5 then makes Friday the first day, and the known Friday 20 November 2015 has weekday number 1.

Language chooses a default, then DATEFIRST can override it

sys.syslanguages lists each language’s default datefirst. SET LANGUAGE restores that language’s default, so apply any deliberate SET DATEFIRST override after it. These are connection settings, not a change to every user on the server.

Changing DATEFIRST affects DATEPART(weekday) numbering; it does not change the actual date. DATEDIFF(week) uses Sunday boundaries regardless of DATEFIRST. Do not expect it to become a Monday-based week-difference function after SET DATEFIRST 1.

For an application report, make the required interpretation explicit and use unambiguous date values. Changing a login’s language globally just to fix one report could alter other date parsing and language behavior. This test restores the previous language and DATEFIRST; use a private session if you have other custom date-format settings.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.