DATETIME Calculations, Part 3

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  */);

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

-- Last Tuesday (inclusive)  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:

-- Last Sunday (Inclusive)  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:

DATEPART(weekday, GETDATE() + @@  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

DATEPART(weekday, GETDATE() + @@  DATEFIRST - 1 /* datefirst is  Monday */) - 1

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

DATEPART(weekday, GETDATE() + @@  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:

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

-- Last Sunday   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:

SELECT DATEADD(day, DATEDIFF(day,  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

-- Last Monday (exclusive)  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.