# SQL Date Calculation Simplifications in SQL Server

By: Aaron Bertrand | Updated: 2022-09-13 | Comments | Related: More > 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:

- Simplify Date Period Calculations in SQL Server
- Creating a date dimension or calendar table in SQL Server
- Using a calendar table in SQL Server - Part 1
- Calendar Table in SQL Server to Identify Date Gaps and Islands
- Create a Calendar Table in SQL Server to Optimize Scheduling Problems
- SQL Server Date Tips

##### About the author

**View all my tips**

Article Last Updated: 2022-09-13