Datetime calculations are challenging. They often involve tricky logic. In the past
three columns, I've gone into depth about datetime calculations, covering various types, including calculating the date of the first and last days of a month based on a
given event datetime value, and calculating the last and next occurrences of a weekday.
This month, I present techniques for calculating the date of the first and last occurrences
of a weekday in a month, based on a given event date—for example, calculating the
first occurrence of a Monday in the current month or calculating the last occurrence
of a Monday in the current month. As usual, in my examples, I use the GETDATE()
function as the input event datetime value, but the techniques I present will work for
any input event datetime value. In this article, I also discuss techniques to identify week
boundaries (e.g., start and end of week).
Calculating First and Last Weekday
If you've kept up with the previous articles in the series, you're familiar with the techniques to calculate the date of the first/last day of the month, as well as the date of the
last/next occurrence of a certain weekday. As a reminder, here's one of the techniques
I showed to calculate the date of the first day of the current month:
SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0);
The logic of this technique is as follows: Calculate the difference in terms of months
between an anchor date at midnight (in this case, 0, representing the base date January 1,
1900) and the event date. Call this difference diff. Add diff months to the anchor date.
And here's one of the techniques I shared to calculate the next occurrence of a
weekday, inclusive (in this example, next Monday):
SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()-1) /7*7 + 7, 0);
The logic of this technique is as follows: Calculate the difference in terms of days
between an anchor date known to be a Monday (0, in this case) and the day before
the event date. Call that difference diff. Divide diff by 7, multiply by 7, and add 7 to get
the difference between the anchor date and the next Monday. Call the result new_diff.
Add new_diff days to the anchor date to get the date of the next Monday (inclusive).
Remember that you need to use an anchor date that reflects the weekday you're after.
For example, I used 0 here because the integer 0 converted to datetime yields the base
date January 1, 1900, which is known to be a Monday. If you wanted the next Tuesday,
you would use 1 instead of 0 as the anchor date.
With this reminder, you're ready for
the calculations that are the focus of this
section. Suppose you need to return the
date of the first occurrence of a Monday
in this month. You might have already
guessed that you need to combine the last
two calculations I presented: Calculate the
date of the first day of the current month
(call it fmd), then calculate the date of the
next occurrence of a Monday in respect
to fmd:
SELECT DATEADD(day, DATEDIFF
(day, 0, DATEADD (month,
DATEDIFF (month, 0, GETDATE
()), 0) -- fmd -1)/7*7 + 7, 0);
To calculate the date of the first Tuesday
of the current month, simply use the
anchor date 1 ( January 2, 1900) instead
of 0 ( January 1, 1900):
SELECT DATEADD(day, DATEDIFF
(day, 1, DATEADD(month,
DATEDIFF(month, 0, GETDATE
()), 0) -- fmd -1) /7*7 + 7, 1);
To calculate the date of the last occurrence
of a weekday in the month, you need to
combine two calculations: the date of the
last day of the month and the date of the
last occurrence of a weekday, inclusive. As
a reminder, here's the technique I shared
in the previous articles to calculate the
date of the last day of the current month
(call it lmd):
SELECT DATEADD(month, DATEDIFF
(month, 0, GETDATE())+1, 0)-1;
And here's the technique I shared to
return the date of the last occurrence of a
weekday (in this example, Monday):
SELECT DATEADD(day, DATEDIFF
(day, 0, GETDATE()) /7*7, 0);
Combine the two techniques to get the date
of the last occurrence of a Monday in the
current month (in respect to the date of the
last day of the current month):
SELECT DATEADD(day, DATEDIFF(day, 0, (DATEADD(month, DATEDIFF
(month, 0, GETDATE())+1, 0)-1)
-- lmd) /7*7, 0);
As before, to get the last occurrence of a
Tuesday in the current month, simply replace the anchor date (e.g., 1 instead of 0):
SELECT DATEADD(day, DATEDIFF(day, 1, (DATEADD(month, DATEDIFF
(month, 0, GETDATE())+1, 0)-1)
-- lmd) /7*7, 1);