Stable Weekday Numbers Without Changing DATEFIRST

I calculate stable weekday numbers when a report needs Monday to mean one. A fixed Monday anchor makes that convention explicit. The calculation doesn’t change DATEFIRST or ask the session for a translated weekday name.

Choose the convention first

The example maps Monday through Sunday to one through seven. That mapping is a business convention, so I state it before choosing an expression. Another report can legitimately need Sunday first.

The anchor is January 1, 1900, which was a Monday. CONVERT with style 112 makes its year, month and day unambiguous. The supplied inputs also become date values before arithmetic, avoiding a dependency on the session’s date-literal interpretation.

WITH Dates AS
(
 SELECT CaseId, CONVERT(date,DateText,112) AS InputDate
 FROM (VALUES (1,'18991230'),(2,'18991231'),(3,'19000101'),
              (4,'19000102'),(5,'20241006'),(6,'20241007'),
              (7,CAST(NULL AS varchar(8)))) v(CaseId,DateText)
), Distances AS
(
 SELECT CaseId, InputDate,
        DATEDIFF(day,CONVERT(date,'19000101',112),InputDate) AS DaysFromMonday
 FROM Dates
)
SELECT CaseId, InputDate, DaysFromMonday,
       DaysFromMonday % 7 AS RawRemainder,
       ((DaysFromMonday % 7 + 7) % 7) + 1 AS MondayWeekday
FROM Distances
ORDER BY CaseId;

Count days from the anchor

DATEDIFF uses the day datepart to count crossed date boundaries. Because both operands are dates, the example doesn’t involve partial days or local clock changes. DaysFromMonday exposes that distance alongside each input.

I keep this intermediate column during review. It makes an incorrect anchor easier to detect before a final weekday label hides the arithmetic. The current dates have large positive distances, while the two earlier dates deliberately produce negative distances.

Normalize a negative remainder

A remainder alone isn’t a Monday-based weekday number for dates before the anchor. The first two rows show raw remainders of minus two and minus one. Adding one directly would leave invalid weekday numbers.

The expression first adds seven, then applies modulo seven again. That maps the remainder into zero through six. Adding one finally produces the stated one-through-seven convention, including Saturday and Sunday before the anchor.

Test the wrap at both ends

The example includes Sunday followed by Monday in October 2024. Their expected final values are seven and one. The anchor date and its following Tuesday provide another direct check of the starting convention.

I’d retain both pairs rather than testing only a familiar weekday. A wrong offset can still produce an integer between one and seven. The important question is whether every integer corresponds to the intended day, especially across the week boundary.

Keep missing dates visible

A NULL input date produces NULL through the distance and remainder expressions. It doesn’t become Monday merely because Monday uses the first numeric value. The final row preserves that missing-date case.

If a report needs a default date, make that decision upstream and name it. I don’t hide missing input inside the weekday formula. Doing so could make an incomplete record look like a correctly classified business day.

Avoid changing session conventions

The query contains no SET DATEFIRST or SET LANGUAGE statement. Its result is defined by date arithmetic and the fixed anchor. That helps when the expression is reused by callers with different session settings.

I wouldn’t use the resulting number as proof that a date is a working day. Holidays, local weekends and organization calendars are separate rules. A stable weekday identifier is useful, but it doesn’t replace a maintained business calendar.

Keep the intended domain small and clear

DATEDIFF(day) returns an int, and these typed date inputs stay within its supported range. The example doesn’t calculate tiny time units over long intervals. Changing the datepart would require reviewing both meaning and possible overflow.

For a production expression, I’d retain the anchor and convention in its documentation. Copying only the modulo arithmetic makes the offset look arbitrary. The complete formula explains why pre-anchor dates work and why the final values don’t depend on DATEFIRST.

Write the convention down once, and every caller gets the same answer.

A weekday number is not a calendar policy, it is a stated convention built from a fixed anchor.

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