Finding the First Monday of Every Month in T-SQL

The patch schedule says Monday, but the session says Sunday starts the week. Calculate the first Monday from a known Monday anchor instead of DATEPART weekday. The result stays consistent when connection language and DATEFIRST settings change.

Anchor the Week to a Known Monday

January first, nineteen hundred, is a Monday in SQL Server's date calendar. DATEDIFF day from that anchor supplies an integer day offset. Reduce it modulo seven to map Monday to zero through Sunday to six. Normalize negative remainders too, so dates before the anchor follow the same rule. DATEFIRST does not participate in this calculation.

I avoid weekday names in scheduling logic. DATENAME changes with session language, while DATEPART weekday follows DATEFIRST. Those settings are reasonable for presentation and dangerous as hidden scheduling inputs. Which connection creates your schedule: a job, an application, or an interactive query window? A reliable rule should return the same date from all three.

Calculate the First Monday of One Month

DATEFROMPARTS builds the first day from a year and month. Move forward by the number of days needed to reach Monday. If the month already starts on Monday, the offset is zero. Keep the input as a date rather than a formatted string with ambiguous month and day order. Invalid year or month arguments need validation at the caller.

DECLARE @year int=2026,@month int=9;
DECLARE @month_start date=DATEFROMPARTS(@year,@month,1);
DECLARE @weekday int=((DATEDIFF(day,CONVERT(date,'19000101'),@month_start)%7)+7)%7;
SELECT @month_start AS MonthStart,
       DATEADD(day,(7-@weekday)%7,@month_start) AS FirstMonday;

This result is a calendar date, not a timezone aware execution instant. Your scheduler still needs a local time and a zone rule. Decide what happens when daylight saving changes affect a chosen execution hour. Keep that issue outside the weekday formula, rather than embedding a timestamp conversion that obscures the month calculation.

List Every First Monday of a Year

GENERATE_SERIES supplies month numbers from one through twelve. It needs SQL Server 2022 or later with database compatibility level 160 or higher. CROSS APPLY gives each step a readable name and avoids repeating the formula. The SELECT returns a planned schedule that you can inspect, store, or join to a calendar table.

DECLARE @year int=2026;
SELECT m.MonthStart,
       DATEADD(day,(7-w.MondayIndex)%7,m.MonthStart) AS FirstMonday
FROM GENERATE_SERIES(1,12,1) AS g
CROSS APPLY(VALUES(DATEFROMPARTS(@year,g.value,1))) AS m(MonthStart)
CROSS APPLY(VALUES(((DATEDIFF(day,CONVERT(date,'19000101'),m.MonthStart)%7)+7)%7))
    AS w(MondayIndex)
ORDER BY m.MonthStart;

I store the scheduling rule with the generated dates. Otherwise next year someone copies the rows without knowing why those dates were chosen. A permanent business calendar is better when holidays, closures, and approved exceptions change the date. The formula answers first Monday, while the calendar answers first approved maintenance day. Those can differ without either calculation being wrong.

Generalize to the Nth Weekday

Use a weekday parameter from zero for Monday through six for Sunday. The first occurrence offset is the target minus the month start index, normalized modulo seven. Add seven days for each further occurrence. A fifth occurrence is not guaranteed to exist, so compare the candidate against the month end before returning it.

DECLARE @month_start date=DATEFROMPARTS(2026,2,1);
DECLARE @target_weekday int=0,@occurrence int=5;
IF @target_weekday NOT BETWEEN 0 AND 6 OR @occurrence NOT BETWEEN 1 AND 5
    THROW 50001,'Choose weekday zero through six and occurrence one through five.',1;
DECLARE @start_index int=((DATEDIFF(day,CONVERT(date,'19000101'),@month_start)%7)+7)%7;
DECLARE @candidate date=DATEADD(day,
    (@target_weekday-@start_index+7)%7+7*(@occurrence-1),@month_start);
SELECT CASE WHEN @candidate<=EOMONTH(@month_start) THEN @candidate END AS NthWeekday;

A NULL result means the requested occurrence does not exist in that month. It should not silently mean the next month's first occurrence. Decide how the application reports that case. Invalid input deserves a clear error or validation result. Valid input describing an absent fifth weekday deserves a documented empty answer. Those two conditions require different handling.

Work Backward for the Last Friday

EOMONTH supplies the last calendar day. Map that day to the same Monday based index. Friday is four, so subtract the normalized distance back to four. This avoids taking a fifth Friday from the start and hoping it remains in the month. The method works for the last occurrence of any target weekday.

DECLARE @month_start date=DATEFROMPARTS(2026,9,1);
DECLARE @month_end date=EOMONTH(@month_start);
DECLARE @end_index int=((DATEDIFF(day,CONVERT(date,'19000101'),@month_end)%7)+7)%7;
SELECT DATEADD(day,-((@end_index-4+7)%7),@month_end) AS LastFriday;

Normalize the month input before reusing this pattern in a helper. The example uses a first day explicitly. A general function accepting any date can call DATEFROMPARTS with its year and month. Do not append a day number to a string representation. Clear date construction is easier to review and independent of the session's date format.

Package the Rule in an Inline Function

Create this function in a disposable database first. CREATE FUNCTION starts its own batch, and GO separates the subsequent test. An inline table valued function exposes a relational expression that callers can join or apply. This version accepts a month date, a Monday based weekday, and an occurrence. Invalid or absent requests return no row.

CREATE FUNCTION dbo.NthWeekday
(
    @MonthDate date,@Weekday int,@Occurrence int
)
RETURNS TABLE
AS
RETURN
(
    SELECT c.Candidate AS WeekdayDate
    FROM (VALUES(DATEFROMPARTS(YEAR(@MonthDate),MONTH(@MonthDate),1))) AS m(MonthStart)
    CROSS APPLY(VALUES(((DATEDIFF(day,CONVERT(date,'19000101'),m.MonthStart)%7)+7)%7))
        AS w(StartIndex)
    CROSS APPLY(VALUES(CASE WHEN @Weekday BETWEEN 0 AND 6 AND @Occurrence BETWEEN 1 AND 5
             THEN (@Weekday-w.StartIndex+7)%7+7*(@Occurrence-1) END)) AS n(DayOffset)
    CROSS APPLY(VALUES(DATEADD(day,
        CASE WHEN n.DayOffset<=DATEDIFF(day,m.MonthStart,EOMONTH(m.MonthStart))
             THEN n.DayOffset END,m.MonthStart)))
        AS c(Candidate)
    WHERE @MonthDate IS NOT NULL AND @Weekday BETWEEN 0 AND 6
      AND @Occurrence BETWEEN 1 AND 5 AND c.Candidate<=EOMONTH(m.MonthStart)
);
GO
SELECT WeekdayDate FROM dbo.NthWeekday(CONVERT(date,'20260901'),0,1);

The CASE guards date arithmetic from invalid occurrence values. The interface's empty row behavior needs to be documented. A caller using CROSS APPLY loses its outer row when the function returns none. Use OUTER APPLY when the schedule report must retain the month and show a missing date. Choose that behavior deliberately before building a year of entries.

Test the Settings You Want to Ignore

The following comparison changes DATEFIRST and restores the original setting afterward. Both calls should follow the same anchor rule. Add tests for months beginning on Monday, February in leap and nonleap years, missing fifth weekdays, and dates before nineteen hundred. Test supported date boundaries when your interface permits the full date range.

DECLARE @saved_datefirst int=@@DATEFIRST;
SET DATEFIRST 1;
SELECT WeekdayDate FROM dbo.NthWeekday(CONVERT(date,'20260901'),0,1);
SET DATEFIRST 7;
SELECT WeekdayDate FROM dbo.NthWeekday(CONVERT(date,'20260901'),0,1);
SET DATEFIRST @saved_datefirst;

Check the dates against an independent calendar in your rehearsal. Include the expected date beside each calculated result, so a failed comparison is visible. Keep those expected dates independent of this formula, rather than deriving them with the same arithmetic. Even a wrong calendar prints its dates with perfect confidence.

Handle a First Monday That Falls on a Holiday

If the calculated first Monday is a holiday, decide whether work moves forward, backward, or waits for explicit approval. Join the date to a maintained holiday calendar and store the approved final schedule separately from the base rule. A simple weekday function should not conceal a changing list of closures and operational exceptions.

I separate calculation from approval when a date controls a patch or billing run. Recompute future dates with the same rule, then review exception handling with the owner. The first Monday calculation remains compact and repeatable. Your schedule becomes reliable when timezone, holiday, approval, and missing occurrence behavior are just as explicit as the arithmetic.

Related reading on this blog: Date Boundaries With DATETRUNC and EOMONTH in SQL Server 2022 and Find Business Days Between Dates.

A weekday formula is not a scheduling policy, it is a consistent calendar building block.

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