Interview Question of the Week #036 – What is the difference between @@DATEFIRST and SET DATEFIRST?

When our company began serving clients in different countries, I learned how one small session setting could change a weekly report. The stored dates had not moved, but two connections could assign different weekday numbers to them.

Question: What is the difference between @@DATEFIRST and SET DATEFIRST?

Answer: SET DATEFIRST changes the first day of the week for the current session. @@DATEFIRST reads the current value. The values run from 1 for Monday to 7 for Sunday, so SET DATEFIRST 5 makes Friday the first day.

Try one date under two settings. This script restores the session’s original first-day value when it finishes:

DECLARE @PreviousDateFirst tinyint = @@DATEFIRST;
DECLARE @Date date = '20260104';

SET DATEFIRST 7;
SELECT @@DATEFIRST AS FirstDay,
       DATEPART(weekday, @Date) AS WeekdayNumber,
       (DATEPART(weekday, @Date) + @@DATEFIRST) % 7 AS OriginalNormalizedValue;

SET DATEFIRST 1;
SELECT @@DATEFIRST AS FirstDay,
       DATEPART(weekday, @Date) AS WeekdayNumber,
       (DATEPART(weekday, @Date) + @@DATEFIRST) % 7 AS OriginalNormalizedValue;

SET DATEFIRST @PreviousDateFirst;

The input date is unchanged. Its weekday number changes from 1 to 7, but my original normalized expression returns 1 for this Sunday under both settings. That original expression assigns 1 to Sunday through 6 to Friday and 0 to Saturday; use those labels deliberately in a report. My original post also compared languages. Italian starts its week on Monday and U.S. English on Sunday. Run that comparison in a disposable query session; SET LANGUAGE also changes date parsing settings:

GO
DECLARE @PreviousLanguage sysname = @@LANGUAGE;
DECLARE @PreviousDateFirst tinyint = @@DATEFIRST;

SET LANGUAGE Italian;
SELECT @@LANGUAGE AS SessionLanguage, @@DATEFIRST AS FirstDay;

SET LANGUAGE us_english;
SELECT @@LANGUAGE AS SessionLanguage, @@DATEFIRST AS FirstDay;

SET LANGUAGE @PreviousLanguage;
SET DATEFIRST @PreviousDateFirst;

The script restores the language and first-day values. If your session had a custom DATEFORMAT before this experiment, restore it separately or close the disposable session.

DATEPART(weekday, ...) and ordinary DATEPART(week, ...) can depend on DATEFIRST. An ISO week follows its own calendar rule. If a report groups by week, decide which week definition the business uses and set or calculate it explicitly. A connection’s language default is a poor hidden rule for a global report.

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