In my February and March columns, I talked about challenges related to datetime
calculations. I presented techniques for separating the date and time parts
of a datetime value, returning the first/last day of the month, and calculating
a language-independent weekday number. Now, I want to offer some techniques
for calculating the date of the last/next occurrences of a weekday related to
a specific date (call it the event date)—for example, calculating the
date of the most recent Monday related to today.
All the techniques I present in this article will be based on the calculations
of a language-independent weekday number, which I discussed last month. As a
reminder, I presented two techniques to calculate a language-independent weekday
number. One technique was based on calculating the offset in terms of days between
a base date and the event date (call it diff). The base date had the
same weekday as the one you wanted to set as the logical first day of the week
(e.g., the string 19000101 or the integer 0 for Monday). When converting the
integer 0 to a datetime value, you get the base date January 1, 1900, which
happens to be a Monday. So 0 represents a date that falls on a Monday, 1 represents
a date that falls on a Tuesday, and so on. The expression diff % 7 +
1 produced the weekday number. For example, to calculate the weekday of today's
date, assuming Monday as the first day of the week, you use the expression
SELECT DATEDIFF(day, 0, GETDATE()) % 7 + 1;
If today happens to be a Tuesday, the above expression would return 2. In this article,
I refer to this technique as datediff-based.
Another technique I presented was based on neutralizing the impact of the DATEFIRST
setting on the DATEPART calculation. The expression I used added @@DATEFIRST
days to the event date and subtracted a constant representing the logical first
day of the week that you want to use. For example, to calculate the weekday
of today's date, assuming Monday as the first day of the week, you use the expression
SELECT DATEPART(weekday, GETDATE() + @@DATEFIRST - 1);
Again, if today happens to be a Tuesday, the above expression would return
2. In this article, I refer to this technique as datepart-based.
Last Occurrence of a Weekday
As long as the datediff-based and datepart-based expressions are
completely clear to you, we can proceed. You're now equipped to write expressions
that calculate the last or next occurrence of a weekday.
Datediff. Suppose today's date is December 19,
2006 (a Tuesday), and you want to calculate the last occurrence of Monday, which
might be today (that is, the calculation is inclusive). So if today is a Monday,
the expression would return today's date. Because I'm assuming in my example
that today's date is December 19, 2006 (Tuesday), the expression should return
the most recent occurrence of Monday, which is yesterday. Here's the datediff-based
expression that returns the date of the most recent Monday:
-- Last Monday (inclusive)
SELECT DATEADD(day, DATEDIFF
(day, 0, -- Base Monday date
GETDATE()) /7*7, 0); -- Base
Monday date
The DATEDIFF function calculates the
difference in terms of days between a base
date, which is a Monday, and today's date
(call it diff). The expression then divides
diff by 7 and multiplies by 7, practically
subtracting the number of days that passed
since the most recent Monday (call the
result floored_diff). Finally, the expression
adds floored_diff days to the base date,
returning the date of last Monday.
As expected, the expression returns last Monday's date, assuming today is December
19, 2006:
2006-12-18 00:00:00.000
As a side note, to validate the expressions in this article, you can explicitly
specify 20061219 as the date instead of
GETDATE().
To return the most recent Tuesday's date, provide a date that falls on a Tuesday
as the base date (represented by the integer 1 or the string 19000102):
SELECT DATEADD(day, DATEDIFF(day,
1, GETDATE())/7*7, 1);
You'll get today's date (December 19, 2006) as output because the calculation
is inclusive:
2006-12-19 00:00:00.000
To return the most recent Sunday's date, provide a date that falls on a Sunday
as the base date (represented by the integer 6 or the string 19000107):
SELECT DATEADD(day, DATEDIFF(day,
6, GETDATE()) /7*7, 6);
You get the output
2006-12-17 00:00:00.000
Another flooring technique that you can use instead of dividing diff
by 7 and then multiplying it by 7 is to subtract from diff a number representing
the offset (in terms of days) of "today's day of the week" from "the day of
the week you need." For example, if the requested day of the week is Tuesday
(represented by the integer 1 or the string 19000102), this offset would be
expressed as
DATEDIFF(day, 1 /* Base Tuesday
Date */, GETDATE()) % 7
Call this expression offset. Now, embed offset as part of an
expression that calculates the most recent occurrence of a requested day of
the week:
-- Don't run
SELECT DATEADD(day, DATEDIFF(day,
0 /* Base Date */, GETDATE())
- diff - offset, 0 /* Base Date
*/);
Prev. page
[1]
2
3
next page 
