Introduction
A lately published tip SQL Server: Query to Get the Date of First Monday of September by Mohamad Cheaito made me wonder if the first occurrence of desired day of week can be found using a single, recursive SQL statement.
First Occurrence of a Given Day of Week
So let's go through this step by step. First we declare few variables, the date defining the month we are interested in and the day of week we're looking for:
DECLARE @Date AS DATE = CONVERT(date, '10/05/2014', 101);
DECLARE @WeekDay AS INT = 7;
I wanted to be able to define any date, not just the first day of a month, because a typical situation could be a query where the date would be the current date.
So now since the date isn't in the beginning of the month, we have to truncate it properly. To illustrate this, I used a separate query like this:
SELECT @date AS DefinedDate,
DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date), 0) AS TruncatedDate;
What the query does is, it calculates how many months have elapsed since day zero in SQL Server. The amount of months is then added to the day zero so the result is the first day of the given month. When the query above is run, the result is:
DefinedDate TruncatedDate
----------- -----------------------
2014-10-05 2014-10-01 00:00:00.000
Now to make a recursive query to generate rows, you can refer to Generating desired amount of rows in SQL using CTE. Using the same idea described in that tip, we can generate dates until we hit the desired day of week. Like this:
WITH DateLooper (CurrentDate, DayNum) AS (
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date), 0) AS CurrentDate,
DATEPART(WEEKDAY, DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date), 0)) AS DayNum
UNION ALL
SELECT DATEADD(DAY, 1, dl.CurrentDate) AS CurrentDate,
DATEPART(WEEKDAY, DATEADD(DAY, 1, dl.CurrentDate)) AS DayNum
FROM DateLooper dl
WHERE DATEPART(WEEKDAY, dl.CurrentDate) <> @WeekDay
)
SELECT dl.CurrentDate,
dl.DayNum
FROM DateLooper dl
ORDER BY dl.CurrentDate;
As you see, the date truncation is used in the anchoring SELECT statement. This way, the result of the anchoring statement is the first day of month and the weekday number for that date.
The recursive SELECT portion adds one day until the given weekday is found. So the result of the query would be:
CurrentDate DayNum
----------------------- ------
2014-10-01 00:00:00.000 4
2014-10-02 00:00:00.000 5
2014-10-03 00:00:00.000 6
2014-10-04 00:00:00.000 7
So far so good. We actually already see the result. The first Saturday of October in 2014 is the 4th day (Sunday is the first day of week with these queries).
Now we only need to make a small adjustment in order to return only one row, the desired date. To achieve this, let's sort the data based on the current date, but this time descending. With descending sort order, let's select just TOP 1 row, like this:
WITH DateLooper (CurrentDate, DayNum) AS (
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date), 0) AS CurrentDate,
DATEPART(WEEKDAY, DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date), 0)) AS DayNum
UNION ALL
SELECT DATEADD(DAY, 1, dl.CurrentDate) AS CurrentDate,
DATEPART(WEEKDAY, DATEADD(DAY, 1, dl.CurrentDate)) AS DayNum
FROM DateLooper dl
WHERE DATEPART(WEEKDAY, dl.CurrentDate) <> @WeekDay
)
SELECT TOP 1
dl.CurrentDate,
dl.DayNum
FROM DateLooper dl
ORDER BY dl.CurrentDate DESC;
As you already see, the result is
CurrentDate DayNum
----------------------- ------
2014-10-04 00:00:00.000 7
You can experiment different situations by changing the values of the @Date and @WeekDay variables.
Last Occurrence of Given Day of Week
What about the last occurrence of the desired day of week? Well, you need just to turn around the direction of the recursion and define the last day of month as starting point.
The last day of month is calculated basically the way as the first day of month. Only this time, an extra month is added and then the result is backed one day:
SELECT DATEADD( DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date) + 1, 0)) AS LastDayOfMonth;
The result for that query would be:
LastDayOfMonth
-----------------------
2014-10-31 00:00:00.000
Since the idea of the query is already explained, let's jump straight to the final version:
WITH DateLooper (CurrentDate, DayNum) AS (
SELECT DATEADD( DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date) + 1, 0)) AS CurrentDate,
DATEPART(WEEKDAY, DATEADD( DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date) + 1, 0))) AS DayNum
UNION ALL
SELECT DATEADD(DAY, -1, dl.CurrentDate) AS CurrentDate,
DATEPART(WEEKDAY, DATEADD(DAY, -1, dl.CurrentDate)) AS DayNum
FROM DateLooper dl
WHERE DATEPART(WEEKDAY, dl.CurrentDate) <> @WeekDay
)
SELECT TOP 1
dl.CurrentDate,
dl.DayNum
FROM DateLooper dl
ORDER BY dl.CurrentDate ASC;
As mentioned, the recursion is now done in descending order and the starting date is the last day of month. Basically, all other elements are the same. Just notice that the outer query selecting TOP 1 record is now ordering the data in ascending order since we want to return the earliest date. So the result is:
CurrentDate DayNum
----------------------- ------
2014-10-25 00:00:00.000 7
That's it this time. :)
References
Some functions used in the code are listed below:
History
- 13th September, 2014: Tip created
