Weekday Logic That Works Under Any DATEFIRST Setting

DATEPART(weekday, OrderDate) can label Sunday as 1 in one session and 7 in another. DATEFIRST is a session setting that can follow a login's language, so a weekend report can change for a British English user. Use a setting-independent weekday number and test a complete year.

Reproduce the DATEFIRST Difference

SET DATEFIRST chooses which weekday is numbered 1. The login's default language sets the starting value, and a session can override it. In two lab connections using different login languages, run the same Sunday query and record @@DATEFIRST alongside the result. The following batch simulates those two sessions without creating logins.

DECLARE @sunday date = '2025-01-05';
SET LANGUAGE us_english;
SELECT @@LANGUAGE AS language_name, @@DATEFIRST AS datefirst_value,
       DATEPART(weekday,@sunday) AS weekday_number;
SET LANGUAGE British;
SELECT @@LANGUAGE AS language_name, @@DATEFIRST AS datefirst_value,
       DATEPART(weekday,@sunday) AS weekday_number;

Run the first SELECT under a lab login with U.S. English default and the second under one with British English default to reproduce the login-level failure. Do not assume every deployment has those exact defaults; display the actual setting. I have seen a report work in one SSMS window and fail in a job connection because the sessions used different language settings.

Normalize DATEPART for Any DATEFIRST Value

If you already have DATEPART(weekday), offset its result by the current first-day setting. The expression below returns Monday=1 through Sunday=7 under every setting from 1 through 7. A normalized result of 6 or 7 is a weekend.

DECLARE @d date = '2025-01-05';
SELECT DATEPART(weekday,@d) AS session_weekday,
       ((DATEPART(weekday,@d) + @@DATEFIRST - 2) % 7) + 1
         AS monday_based_weekday,
       CASE WHEN ((DATEPART(weekday,@d) + @@DATEFIRST - 2) % 7) + 1
                  IN (6,7)
            THEN 1 ELSE 0 END AS is_weekend;

The offset works because DATEPART and the first-day setting come from the same session. Do not store the raw DATEPART number and normalize it later under another session's setting; save either the date or the already normalized value. I keep the expression in one named computed step rather than repeating it across a complex report.

Count From a Known Monday

A second method ignores the first-day setting entirely. January 1, 1900 was a Monday in SQL Server's date arithmetic. Count days from that anchor, reduce modulo seven, and shift to 1 through 7. Normalize a negative remainder for dates before the anchor.

DECLARE @d date = '2025-01-05';
SELECT ((DATEDIFF(day, CONVERT(date,'19000101'), @d) % 7
          + 7) % 7) + 1 AS monday_based_weekday;

This calculation is stable across languages because it uses elapsed days, not weekday names or first-day numbering. Use an unambiguous date literal and an explicit date type. For SQL Server 2022, DATETRUNC(iso_week, date) can also help find a Monday week boundary, but the simple day-offset formula works on older versions.

Test a Full Year Under All Settings

A few familiar dates can hide an off-by-one error at year boundaries. Generate every date in a leap year and compare the two formulas under each of the seven settings. Count mismatches and confirm the weekend total by inspecting known Saturdays and Sundays. The sample below tests one setting; repeat it with the other six values in the lab.

SET DATEFIRST 7;
;WITH n AS
(
    SELECT TOP (366)
           ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS day_offset
    FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b
), dates AS
(
    SELECT DATEADD(day,day_offset,CONVERT(date,'20240101')) AS d
    FROM n
)
SELECT COUNT(*) AS mismatch_count
FROM dates
WHERE ((DATEPART(weekday,d)+@@DATEFIRST-2)%7)+1
   <> ((DATEDIFF(day,CONVERT(date,'19000101'),d)%7+7)%7)+1;

Mismatch count should be zero. Use a loop over the seven values for automated regression testing, or run seven explicit batches and save the results. The test includes February 29 and both year edges. A date-only value avoids time-zone and midnight conversion distractions in this weekday rule.

Keep Display Names Separate

DATENAME(weekday, d) follows language settings too, so it is unsuitable as a stable stored key. Calculate the normalized number for logic, then display a localized day name for users if needed. If a business calendar has holidays or region-specific weekends, a calendar table can own those rules. A universal Saturday/Sunday assumption is not enough for every operation.

I prefer a calendar table for reporting dimensions and a local expression for small filters. Whichever path is used, test the same dates under the job account and a normal user account. A report that changes when a login's default language changes is a hidden dependency worth removing.

Separate Login Defaults From Query Rules

A login's default language influences its starting session settings, including weekday numbering. Application code can change the setting for its own session, so even two calls from the same login can differ. For a diagnosis, capture @@LANGUAGE and @@DATEFIRST in the exact connection that ran the report. Do not infer them from the DBA's SSMS window.

A test using two lab logins is useful because it reproduces the user report, but the calculation should not depend on those accounts. After the fix, run the same input dates under both and compare output rows. The normalized weekday should be identical, while localized display text can differ by design.

Avoid String-Based Weekday Checks

A predicate such as DATENAME(weekday, d) IN ('Saturday','Sunday') changes with language and can fail for localized names. Comparing a formatted date string has similar problems and can make an indexed date column harder to search. Use a numeric rule based on the date, or join to a calendar table with a business weekend flag. Keep text formatting at the display layer.

For an international business, a Saturday/Sunday weekend is not universal. The stable Monday-based number is a technical building block; the business rule can map different weekdays to working and nonworking days by region. That mapping belongs in a calendar dimension with effective dates when holidays and local schedules matter.

Verify Every DATEFIRST Value

The seven settings are a small finite test space. A loop can set each value in turn, run the full-year comparison, and record mismatch counts. Also inspect specific Mondays and Sundays around New Year, because visual review catches an inverted mapping quickly. If any setting yields a mismatch, do not patch just that session; revisit the arithmetic and anchor.

I keep the normalized formula in one view or function used by the report rather than pasting variants into several procedures. A single tested expression lowers the chance that one report uses Monday=0 and another Monday=1. Include the numbering convention in its name or documentation.

Does the report mean the weekday defined by the session, or one fixed weekday rule?

Related reading on this blog: Interview Question of the Week #046: How @@DATEFIRST and SET DATEFIRST Are Related? and Finding Day Name from Date.

A weekday number is not a universal label, it is a value shaped by DATEFIRST unless normalized.

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