Simplify SQL Date Calculations - First, Last, Nth Day of the Month

By: Aaron Bertrand | Updated: 2022-09-13 | Comments (1) | Related: 1 | 2 | 3 | 4 | 5 | 6 | > Dates

Problem

In a previous tip, Simplify Date Period Calculations in SQL Server, I described how to easily calculate certain dates, like the first day of a given month, quarter, or year. Another common scenario is trying to find the last weekday of the month, or the n^th Monday or Wednesday. In this tip, I will show ways to simplify these calculations, both with and without a calendar table.

Solution

First, I want to explain why problems involving days of the week are challenging.

DATEFIRST

Different parts of the world use a different weekday to denote the "start" of the week, and so SQL Server has a setting called DATEFIRST that honors this, though it probably doesn't work in a way that is intuitive for everyone.

From the documentation for SET DATEFIRST:

Why is this unintuitive? Well, many people expect the DATEFIRST value to make DATEPART(WEEKDAY match the "first" day of the week (either the one they know or the one they set it to). However, what it does is it shifts the weekday indicated for a given date. Let's take a known Sunday, September 4^th, 2022, and see what it returns under various SET DATEFIRST settings:

DECLARE @d date = '20220904';                  -- Sunday is weekday:
SET DATEFIRST 1; SELECT DATEPART(WEEKDAY, @d); -- 7
SET DATEFIRST 2; SELECT DATEPART(WEEKDAY, @d); -- 6
SET DATEFIRST 3; SELECT DATEPART(WEEKDAY, @d); -- 5
SET DATEFIRST 4; SELECT DATEPART(WEEKDAY, @d); -- 4
SET DATEFIRST 5; SELECT DATEPART(WEEKDAY, @d); -- 3
SET DATEFIRST 6; SELECT DATEPART(WEEKDAY, @d); -- 2
SET DATEFIRST 7; SELECT DATEPART(WEEKDAY, @d); -- 1

The problem this creates is that you can't reliably use DATEPART(WEEKDAY directly to determine if a day is, in fact, a Sunday. You could hard-code SET DATEFIRST to be the one you expect and override end users' settings, but this can be problematic because you're not always in control of the end users' code, and you can't put SET inside a function (where many people encapsulate complicated date logic).

LANGUAGE

Some will say, "just use the name," but this creates a different issue: the name of a weekday is language-dependent. SQL Server also supports 34 different languages, including:

DECLARE @d date = '20220904';                          -- Sunday's name is:
SET LANGUAGE Deutsch;    SELECT DATENAME(WEEKDAY, @d); -- Sonntag
SET LANGUAGE Français;   SELECT DATENAME(WEEKDAY, @d); -- dimanche
SET LANGUAGE Nederlands; SELECT DATENAME(WEEKDAY, @d); -- zondag
SET LANGUAGE Norsk;      SELECT DATENAME(WEEKDAY, @d); -- søndag
SET LANGUAGE Türkçe;     SELECT DATENAME(WEEKDAY, @d); -- Pazar

So, you can't reliably use this function, either, to determine if a day is a Sunday, short of using an IN () list with values for all 34 languages (and hoping for no conflicts). As with DATEFIRST, you could override the user's settings, but this could have unintentional side effects (such as misinterpreting strings or presenting error or warning messages in the wrong language), and you still can't put SET inside a function.

What to Do Instead

To avoid the complications of regional or session settings, you can use date math to apply a consistent weekday number to each day of the week regardless of DATEFIRST or LANGUAGE settings. Let's put 10 rows into a simple table:

CREATE TABLE dbo.SimpleDates(TheDate date);
INSERT dbo.SimpleDates (TheDate) VALUES ('20220903'),
    ('20220904'),('20220905'),('20220906'),
    ('20220907'),('20220908'),('20220909'),
    ('20220910'),('20220911'),('20220912');

Then we can look at the DATEPART(WEEKDAY values under different DATEFIRST settings:

SET DATEFIRST n; -- 1 to 7
SELECT TheDate, 
       [dayname] = DATENAME(WEEKDAY, TheDate), 
       [weekday] = DATEPART(WEEKDAY, TheDate) 
FROM dbo.SimpleDates;

Here is the massaged output:

While that may look like a big sudoku puzzle, there is a beautiful hidden pattern that we can use to our advantage. Let's look at the first row, where you can see that @@DATEFIRST and the output of DATEPART(WEEKDAY are rather complementary (1+6 = 7, 2+5 = 7, and so on):

If we take the modulo (%7), now we can get the same value (0) for Saturday for every possible @@DATEFIRST setting.

And if we extend that to all the rows:

SET DATEFIRST n; -- 1 to 7
SELECT TheDate, 
       [dayname]  =  DATENAME(WEEKDAY, TheDate), 
       [adjusted] = (DATEPART(WEEKDAY, TheDate) + @@DATEFIRST) % 7
FROM dbo.SimpleDates;

Now we have something we can consistently use to reference any specific weekday regardless of @@DATEFIRST (and let's highlight our two Sundays):

The important part here is this expression:

(DATEPART(WEEKDAY, <some date>) + @@DATEFIRST) % 7

And we can use that more generally to find the weekdays corresponding to the above output. For example, to find all Sundays, we can now say:

SET DATEFIRST 1;
SELECT TheDate, [dayname] = DATENAME(WEEKDAY, TheDate)
  FROM dbo.SimpleDates
  WHERE (DATEPART(WEEKDAY, TheDate) + @@DATEFIRST) % 7 = 1;

And the output will always be (for any SET DATEFIRST setting):

TheDate      dayname
----------   -------
2022-09-04   Sunday
2022-09-11   Sunday

The dayname will still display depending on the language setting, but that's okay because we're not using that value to filter.

How to Put that to Use

That was a lot of ramp-up, but we can put this technique to quick use, solving a variety of the classes of problems described above.

All the Weekdays

Let's say we want a list of all the Sundays in 2022. We could first generate a list of all 365 or 366 days in the year (and there are many ways to do this, a recursive CTE is just the easiest to demonstrate without other scaffolding like a numbers table or calendar table):

DECLARE @start date = DATEFROMPARTS(2022, 1, 1);
;WITH AllDates(TheDate) AS
(
  SELECT @start
  UNION ALL
  SELECT DATEADD(DAY, 1, TheDate)
    FROM AllDates WHERE TheDate < DATEADD(DAY, -1, DATEADD(YEAR, 1, @start))
)
SELECT TheDate 
  FROM AllDates
  OPTION (MAXRECURSION 366);

To limit that to just Sundays, we can say:

…
SELECT TheDate 
  FROM AllDates
  WHERE (DATEPART(WEEKDAY, TheDate) + @@DATEFIRST) % 7 = 1
  …

…which will yield 52 (or sometimes 53) Sundays, depending on the year. To put this into a function, we could create one that takes the month as input (so we can avoid dealing with MAXRECURSION):

CREATE OR ALTER FUNCTION dbo.GetTheWeekdays
(
  @month   date,
  @weekday tinyint -- 0 = Sat, 1 = Sun, 2 = Mon, 3 = Tue,
                   -- 4 = Wed, 5 = Thu, 6 = Fri
) -- put the key here ^^^^^^^^^^^^^^^^^^^^^^^^^
  -- just remember that Saturday is 0
RETURNS TABLE WITH SCHEMABINDING
AS
  RETURN
  (
    WITH AllDates(TheDate) AS
    (
      SELECT DATEFROMPARTS(YEAR(@month), MONTH(@month), 1)
      UNION ALL
      SELECT DATEADD(DAY, 1, TheDate)
        FROM AllDates 
        WHERE TheDate < DATEADD(DAY, -1, 
          DATEADD(MONTH, 1, DATEFROMPARTS(YEAR(@month), MONTH(@month), 1)))
    )
    SELECT TheDate FROM AllDates
    WHERE (DATEPART(WEEKDAY, TheDate) + @@DATEFIRST) % 7 = @weekday
);

Using this function, we can get all the Sundays in the current month using:

SELECT TheDate FROM dbo.GetTheWeekdays(GETDATE(), 1);

If you want to further filter within that month and have date parameters like @start and @end (inclusive):

SELECT TheDate FROM dbo.GetTheWeekdays(GETDATE(), 1)
  WHERE TheDate >= @start
  AND TheDate < DATEADD(DAY, 1, @end);

And to get multiple months:

;WITH Months(m) AS
(
  SELECT m FROM (VALUES('20220701'),('20220813'),('20220915')) AS m(m)
)
SELECT f.TheDate FROM Months AS m
  CROSS APPLY dbo.GetTheWeekdays(m.m, 1) AS f
  -- WHERE f.TheDate >= @start 
  -- AND f.TheDate < DATEADD(DAY, 1, @end)
;

If you had a calendar table, all of this is easier:

SELECT TheDate
  FROM dbo.Calendar
  WHERE TheYear = 2022 AND TheMonth IN (7,8,9)
  -- AND TheDate >= @start
  -- AND TheDate < DATEADD(DAY, 1, @end)
  AND TheDayName = 'Sunday';

The nth Weekday

Another requirement I see often is to return the 2^nd Sunday or the 3^rd Wednesday. We can make this easy by adding a row number inside the function, e.g.

CREATE OR ALTER FUNCTION dbo.GetTheWeekdays
(
  @month   date,
  @weekday tinyint -- 0 = Sat, 1 = Sun, 2 = Mon, 3 = Tue,
                   -- 4 = Wed, 5 = Thu, 6 = Fri
)
RETURNS TABLE WITH SCHEMABINDING
AS
  RETURN
  (
    WITH AllDates(TheDate) AS
    (
      SELECT DATEFROMPARTS(YEAR(@month), MONTH(@month), 1)
      UNION ALL
      SELECT DATEADD(DAY, 1, TheDate)
        FROM AllDates
        WHERE TheDate < DATEADD(DAY, -1,
          DATEADD(MONTH, 1, DATEFROMPARTS(YEAR(@month), MONTH(@month), 1)))
    )
    SELECT TheDate, nthWeekday = ROW_NUMBER() OVER (ORDER BY TheDate)
      FROM AllDates
      WHERE (DATEPART(WEEKDAY, TheDate) + @@DATEFIRST) % 7 = @weekday
  );

Then for the same three months, we can get the 2^nd Sunday by passing in 1 and then filtering on nthWeekday (we could have also changed the function to take the n^th number as a parameter):

;WITH Months(m) AS
(
  SELECT m FROM (VALUES('20220701'),('20220813'),('20220915')) AS m(m)
)
SELECT * FROM Months AS m
  CROSS APPLY dbo.GetTheWeekdays(m.m, 1) AS f
  WHERE f.nthWeekday = 2
  -- AND f.TheDate >= @start
  -- AND f.TheDate < DATEADD(DAY, 1, @end)
;

In a calendar table, the n^th day of the week in a given month can be obtained using a simple predicate, for example, the 2^nd Sunday of any month:

…
WHERE TheDayName = 'Sunday'
AND TheDayOfWeekInMonth = 2

We could do something similar to find the n^th last weekday, such as the 2^nd last Sunday, by simply adding another row number using ORDER BY TheDate DESC.

CREATE OR ALTER FUNCTION dbo.GetTheWeekdays
(
  @month   date,
  @weekday tinyint -- 0 = Sat, 1 = Sun, 2 = Mon, 3 = Tue,
                   -- 4 = Wed, 5 = Thu, 6 = Fri
)
RETURNS TABLE WITH SCHEMABINDING
AS
  RETURN
  (
    WITH AllDates(TheDate) AS
    (
      SELECT DATEFROMPARTS(YEAR(@month), MONTH(@month), 1)
      UNION ALL
      SELECT DATEADD(DAY, 1, TheDate)
        FROM AllDates
        WHERE TheDate < DATEADD(DAY, -1,
          DATEADD(MONTH, 1, DATEFROMPARTS(YEAR(@month), MONTH(@month), 1)))
    )
    SELECT TheDate,
        nthWeekday = ROW_NUMBER() OVER (ORDER BY TheDate)
      FROM AllDates
      WHERE (DATEPART(WEEKDAY, TheDate) + @@DATEFIRST) % 7 = @weekday
  );

The Last Weekday (or Non-Weekend Day)

Building on the latest iteration of the function, we can flip it around to return all dates instead of passing in a filter:

CREATE OR ALTER FUNCTION dbo.GetAllDays
(
  @month date
)
RETURNS TABLE WITH SCHEMABINDING
AS
  RETURN 
  (
    WITH AllDates(TheDate) AS
    (
      SELECT DATEFROMPARTS(YEAR(@month), MONTH(@month), 1)
      UNION ALL
      SELECT DATEADD(DAY, 1, TheDate)
        FROM AllDates
        WHERE TheDate < DATEADD(DAY, -1,
          DATEADD(MONTH, 1, DATEFROMPARTS(YEAR(@month), MONTH(@month), 1)))
    ),
    TheseDates AS
    (
      SELECT TheDate,
        wdn = (DATEPART(WEEKDAY, TheDate) + @@DATEFIRST) % 7
      FROM AllDates
)
    SELECT TheDate,
        WeekdayNumber = wdn,
        nthWeekday     = ROW_NUMBER() OVER (PARTITION BY wdn ORDER BY TheDate),
        nthLastWeekday = ROW_NUMBER() OVER (PARTITION BY wdn ORDER BY TheDate DESC)
      FROM TheseDates
  );

With this function, we can again find the last weekday of the month using the following query:

;WITH Months(m) AS
(
  SELECT m FROM (VALUES('20220701'),('20220813'),('20220915')) AS m(m)
)
SELECT m.m, LastWeekday = MAX(f.TheDate)
  FROM Months AS m
  CROSS APPLY dbo.GetAllDays(m.m) AS f
  WHERE f.WeekdayNumber > 1
    AND nthLastWeekday = 1
    -- AND f.TheDate >= @start
    -- AND f.TheDate < DATEADD(DAY, 1, @end)
  GROUP BY m.m;

With a calendar table, this is again relatively trivial and, of course, can also account for holidays in addition to days of the week:

SELECT TheYear, TheMonth, LastWeekday = MAX(TheDate)
  FROM dbo.Calendar
  WHERE TheDayName NOT IN ('Saturday', 'Sunday')
  -- AND filters for month(s), date range, IsHoliday, etc.
  GROUP BY TheYear, TheMonth;

For completeness, you can do this kind of thing differently, without row numbers or recursion. Since our formula returns 0 for Saturday and 1 for Sunday, we can handle those by saying, essentially, "take the last day of the month – if it's a Saturday, subtract 1 day; if it's a Sunday, subtract two days; otherwise, subtract none. That will yield the last weekday."

CREATE OR ALTER FUNCTION dbo.LastWeekdayOfMonth
(
  @month date
)
RETURNS TABLE WITH SCHEMABINDING
AS
  RETURN
  (
    SELECT LastWeekday = DATEADD(DAY,
      CASE WHEN dp < 2 THEN -dp-1 ELSE 0 END, eom)
    FROM
    (
      SELECT eom = EOMONTH(@month),
        dp = (DATEPART(WEEKDAY, EOMONTH(@month)) + @@DATEFIRST) % 7
    ) AS x
  );

I've made fun of EOMONTH() before but, in my defense, that was more than a decade ago, when I thought it was simply being introduced to make BETWEEN easier. It is actually quite handy in cases like this.

Now you can get the same results as above using:

;WITH Months(m) AS
(
  SELECT m FROM (VALUES('20220701'),('20220813'),('20220915')) AS m(m)
)
SELECT m.m, f.LastWeekday
  FROM Months AS m
  CROSS APPLY dbo.LastWeekdayOfMonth(m.m) AS f;

Next Steps

If you find yourself struggling with day-of-week problems, consider a calendar table or helper functions using @@DATEFIRST and %7 as described here. Remembering a new enum for weekday numbers might be easier than your current challenges. Also, see these tips and other resources:

About the author

Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2022-09-13

SQL Date Calculation Simplifications in SQL Server