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

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

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:

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:

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):

1, GETDATE())/7*7, 1);

You'll get today's date (December 19, 2006) as output because the calculation is inclusive:

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):

6, GETDATE()) /7*7, 6);

You get the output

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

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:

SELECT DATEADD(day, DATEDIFF(day,

0 /* Base Date */, GETDATE())

- diff - offset, 0 /* Base Date

*/);

In this case—unlike the expression calculating *offset*—the base date you use doesn't really matter, as long as you specify the same base date in both the DATEDIFF function and the DATEADD function. The complete expression looks like

SELECT DATEADD(day, DATEDIFF

(day, 0 /* Any Base Date

*/, GETDATE()) -- diff

(DATEDIFF(day, 1 /* Base

Tuesday Date */, GETDATE()) %

7), -- offset 0 /* Any Base

Date */);

If you're after the most recent Sunday's date, all you need to do is specify a Sunday base date (the integer 6 or the string 19000107) instead of a Tuesday base date:

SELECT DATEADD(day, DATEDIFF(day,

0 /* Any Base Date */,

GETDATE()) - (DATEDIFF(day,

6 /* Base Sunday Date */,

GETDATE()) % 7), 0 /* Any Base

Date */);

* Datepart.* You can also rely on the

*datepart-based*technique to calculate

*offset*. Remember that the

*datepart-based*technique calculates a language-independent weekday number. If you think about it,

*offset*is nothing more than a weekday number minus one, assuming the weekday you're looking for is the first day of the week. The following expression uses the

*datepart-based*technique to calculate a language-independent weekday number, assuming Monday is the first day of the week:

DATEFIRST - 1 /* datefirst is

Monday */)

Don't confuse this expression's constant (1 for Monday) with the constants in the *datediff-based* technique. This time, the constant doesn't represent a base date as an integer; rather, it represents the logical first day of the week you want to set—1 for Monday, 2 for Tuesday, and so on. So, to get *offset *(weekday number minus one), assuming Monday is the first day of the week, use the expression

DATEFIRST - 1 /* datefirst is

Monday */) - 1

To get *offset *assuming Tuesday as the first day of the week, subtract the constant 2 from @@DATEFIRST:

DATEFIRST - 2 /* datefirst is

Tuesday */) - 1

Finally, embed the new *offset* calculation in the complete expression that returns the last occurrence of a weekday—for example, the most recent Tuesday:

SELECT DATEADD(day, DATEDIFF(day,

0 /* Base Date */, GETDATE()) -

(DATEPART(weekday, GETDATE() +

@@DATEFIRST - 2 /* datefirst is

Tuesday */) - 1), 0 /* Base

Date */);

To return last Sunday's date, subtract the constant 7 from @@DATEFIRST:

SELECT DATEADD(day, DATEDIFF(day,

0 /* Base Date */, GETDATE()) -

(DATEPART(weekday, GETDATE() +

@@DATEFIRST - 7 /* datefirst is

Sunday */) - 1), 0 /* Base

Date */);

I find this article's first *datediff-based *technique to be the most elegant way to calculate the last occurrence of a weekday. Doubtless, it's the shortest. So, from this point on, I'll rely on this technique for other calculations as well. As a reminder, here's the expression I used to calculate the last occurrence of a Monday:

0, GETDATE()) /7*7, 0);

Twice, you specify a base date representing the weekday you're after (the integer 0 or the string 19000101 for a Monday base date).

Suppose you need the calculation of the last occurrence of a weekday to be exclusive—not to take the event date into consideration. All you need to do is subtract 1 from the event date. For example, to produce the most recent occurrence of Monday in an exclusive manner, you would use the expression

SELECT DATEADD(day, DATEDIFF(day,

0, GETDATE()-1) /7*7, 0);

This method is applicable to all the techniques that I demonstrated earlier. Simply subtract 1 from the event date to make the calculation exclusive.

### Next Occurrence of a Weekday

If you need to produce the next occurrence of a weekday corresponding to a given event date, you can rely on techniques that are similar to those I presented for last occurrence. You might think that to produce the next occurrence of a weekday, all you need to do is add 7 days to the calculation of the last occurrence of that weekday. But the calculation is trickier than you think. To make the calculation of the next occurrence *inclusive*, you need to add 7 days to the *exclusive* calculation of the last occurrence. Think about it: If today is Tuesday, the inclusive calculation of the last occurrence of Tuesday will yield today's date. When you add 7 days, you end up with a Tuesday date a week ahead of today—not with today's date. However, if you use the exclusive calculation of last Tuesday's date, you will get the Tuesday date a week ago. Adding 7 days gets you today's date, effectively making the calculation of the next occurrence of a weekday inclusive. Similarly, to make the calculation of the next occurrence exclusive, you need to add 7 days to the inclusive calculation of the last occurrence.

To clarify this idea, Figure 1 shows a few examples, assuming today's date is December 19, 2006 (Tuesday). As you can probably figure out, instead of adding 7 days, you can add (or subtract) any multiplication of 7 days to get the next/last occurrence of a weekday several weeks ahead/ago.

### Messing with Your Head

You might think my goal is to mess with your head. Unfortunately, there are no built-in functions that perform such calculations, and the techniques I'm sharing are as simple as you can get. Because calculations such as the last/next occurrence of a weekday are frequently needed, it's good to be familiar with the techniques I've presented. But we're not done yet. Next month, I'll discuss datetime-related calculations further. If you think the calculations in this article were tricky, just wait. In the meantime, get involved! Do you have datetime solutions you'd like to share? Check out the sidebar "Share Your DATETIME Thoughts" for some great reader responses.