DATETIME Calculations, Part 4

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

Of course, you can use similar techniques to calculate the date of the first/last occurrence of a weekday in a year; simply use the date part year instead of month. For example, to calculate the date of the first occurrence of a Monday in the current year, you can use

SELECT DATEADD(day, DATEDIFF(day, 0, DATEADD(year, DATEDIFF(year,0, GETDATE()), 0) -- fmd -1)/7*7 + 7, 0);

To calculate the date of the first occurrence of a Tuesday in the current year, use

SELECT DATEADD(day, DATEDIFF(day, 1, DATEADD(year, DATEDIFF(year,0, GETDATE()), 0) -- fmd-1)  /7*7 + 7, 1);

To calculate the date of the last occurrence of a Monday in the current year, use

SELECT DATEADD(day, DATEDIFF(day, 0,(DATEADD(year, DATEDIFF(year,0, GETDATE())+1, 0)-1) -- lmd) /7*7, 0);

To calculate the date of the last occurrence of a Tuesday in the current year, use

SELECT DATEADD(day, DATEDIFF(day, 1,(DATEADD(year, DATEDIFF(year,0, GETDATE())+1, 0)-1) --lmd) /7*7, 1);

Identifying Week Boundaries

The calculations to identify the date of the next/last occurrence of a weekday in respect to a given event datetime value are also effective for identifying week boundaries (e.g., week start and end) that correspond to an event datetime value. Suppose that, in your calculations, you want to assume that a week starts on a Monday and ends on a Sunday. If you want to determine the dates of the week boundaries in respect to a given event datetime value (e.g., GETDATE), simply use the calculations for the last occurrence of Monday (inclusive) and the next occurrence of Sunday (inclusive):

SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()) /7*7, 0) ASweekstart;

SELECT DATEADD(day, DATEDIFF(day, 6, GETDATE()-1) /7*7 + 7, 6) ASweekend;

Here's an example of a problem in which you need to calculate week boundaries: Given the sales table in the sample pubs database, you need to aggregate order quantities (i.e., the qty column) by the week (corresponding to the ord_date column). Table 1 shows the desired result.

The following code calculates the weekstart date in respect to ord_date, assuming the week starts on Monday (0 is the anchor date used), generating the output that Table 2 shows:

SELECT ord_date, DATEADD(day, DATEDIFF(day, 0, ord_date)/7*7, 0) AS weekstart,qty  FROM pubs.dbo.sales;

You can now simply group the rows by weekstart, and even return the weekend date by adding six days to weekstart, generating the desired output that Table 1 shows:

SELECT weekstart, weekstart +  6 AS weekend, SUM(qty) AStotalqty FROM (SELECT DATEADD(day, DATEDIFF(day, 0, ord_date)/7*7, 0) AS weekstart, qty FROM pubs.dbo.sales) AS D GROUPBY weekstart;

Remember that if you have full control over which weekday is considered the first day of the week. The last example used Monday as the start of the week. If you want to consider Sunday as the start of the week, change the anchor date accordingly to 6 (representing January 7, 1900):

SELECT weekstart, weekstart +  6 AS weekend, SUM(qty) AStotalqtyFROM (SELECT DATEADD(day,  DATEDIFF(day, 6, ord_date)/7*7, 6) AS weekstart, qtyFROM pubs.dbo.sales) AS DGROUP BY weekstart;

Getting Trickier

You're probably realizing that more and more of these datetime calculations involve tricky logic. To improve your logic, remember to practice the pure logic puzzles presented in the Logical Puzzle sidebar.