Hack 23. Second Tuesday of the Month

You can find "floating" calendar dates, such as the second Tuesday of the month, with modular arithmetic and brute force reasoning.

The formula to calculate the second Tuesday of the month depends on the day of the week of the first day of the month. But which month? Obviously, all you need to know are the year and month, but it's easier if you start with a date: the date of the first day of that month. For testing purposes, use the following table:

CREATE TABLE monthdates(monthdate DATE NOT NULL PRIMARY KEY); INSERT INTO monthdates(monthdate) VALUES (DATE '2007-04-01'); INSERT INTO monthdates(monthdate) VALUES (DATE '2007-05-01'); INSERT INTO monthdates(monthdate) VALUES (DATE '2007-06-01');

So, given a date, the first day of some month, what date is the second Tuesday of that month? The process for obtaining the solution begins by calculating the day of the week for the first day of the month.

4.5.1. Day-of-Week Function

Although standard SQL does not provide a function to give the day of the week for any date, most database systems do. Table 4-5 shows some of the functions that can accomplish this.

Table 4-5. Day-of-the-week functions
Database Function
MySQL DAYOFWEEK(date)
Oracle TO_CHAR(date,'D')
SQL Server DATEPART(DW,date)
PostgreSQL EXTRACT(dow FROM date)
Access DatePart("w", date)

The essence of these functions is that they will return a number between 0 and 6, or between 1 and 7. Sometimes 0 (or 1) is Sunday and 6 (or 7) is Saturday, and sometimes 0 (or 1) is Monday and 6 (or 7) is Sunday.

Make sure you know how your database system is set up, because some database systems have local settings that affect the weekday number returned, such as NLS_TERRITORY in Oracle and DATEFIRST in SQL Server. MySQL offers WEEKDAY(date), which returns 1 (Monday) through 7 (Sunday), as well as DAYOFWEEK(date), which returns 1 (Sunday) through 7 (Saturday).

The following formula uses the range 1 (Sunday) through 7 (Saturday). If your database system has no easy way to produce this range, but you can produce some other similar range, then you can alter the formula easily once you understand how it works.

4.5.2. The Formula

Converting the first day of the month into the second Tuesday of the month simply involves manipulating the day of the week of the first day with an arithmetic formula. Before you see the formula, you should review what happens when the first day of the month falls on each day of the week, from Sunday through Saturday.

If the first day of the month is:

  1. A Sunday, the third is a Tuesday, so the tenth is the second Tuesday.

  2. A Monday, the second is a Tuesday, so the ninth is the second Tuesday.

  3. A Tuesday, the eighth is the second Tuesday.

  4. A Wednesday, the seventh is the next Tuesday, so the fourteenth is the second Tuesday.

  5. A Thursday, the sixth is the next Tuesday, so the thirteenth is the second Tuesday.

  6. A Friday, the fifth is the next Tuesday, so the twelfth is the second Tuesday.

  7. A Saturday, the fourth is the next Tuesday, so the eleventh is the second Tuesday.

This exhausts all possibilities. So the challenge now is simply to reduce these facts into a formula. With the aid of an underappreciated technological methodology called brute force, you can verify the correctness of the following manipulation of the day of the week of the first day of the month, as shown in Table 4-6.

Table 4-6. Demonstration calculation
A1st Bwkday C10B DC mod 7 ED+7
sun 1 9 2 9
mon 2 8 1 8
tue 3 7 0 7
wed 4 6 6 13
thu 5 5 5 12
fri 6 4 4 11
sat 7 3 3 10

The first column (A) is the day of the week of the first day of the month, and the second column is the numerical equivalent of this, using the range 1 (Sunday) through 7 (Saturday).

The important data in Table 4-6 is in the last column, which is the number of days to add to the date of the first day of the month.

So in a nutshell, the formula is:

  1. Find B, the day of the week of the first day of the month, using:

  2. 1=Sunday ... 7=Saturday.

  3. Subtract this number from 10 to get C:

  4. With Sunday=1 ... Saturday=7, Tuesday would be 3.

  5. The number 3 B is the offset (relative to the first of the month) for a Tuesday, but it might be in the current month or the previous month; 10 B is also a Tuesday, and so are 17 B and 24 B.

  6. You should choose to subtract from 10 because you want C to be positive for all inputs. This is because you need D to be positive in the next step, but a negative value for C would result in a negative value for D. This is because 1 % 7 gives 1 on most systems.

  7. Divide by 7 and keep the remainder to get D.

  8. D is also the offset for a Tuesday, and D is in the range 0 to 6. Every day in the first week has an offset between 0 and 6. So D is the first Tuesday of the month.

  9. Add 7 to get E.

  10. That takes the range of E from 7 to 13. Every day in the second week has an offset in the range 713.

  11. Take the result and add that number of days to the date of the first day of the month.

In practical terms, to implement this formula you will need to use the specific date and arithmetic functions of your database system. Here are some examples.

4.5.2.1. MySQL

In MySQL:

SELECT monthdate AS first_day_of_month , DATE_ADD(monthdate , INTERVAL ( ( 10 - DAYOFWEEK(monthdate) ) % 7 ) + 7 DAY ) AS second_tuesday_of_month FROM monthdates

 

4.5.2.2. Oracle

In Oracle:

SELECT monthdate AS first_day_of_month , monthdate + MOD( ( 10 TO_CHAR(monthdate,'d') ), 7 ) + 7 AS second_tuesday_of_month FROM monthdates

 

4.5.2.3. SQL Server

With SQL Server:

SELECT monthdate AS first_day_of_month , DATEADD(day , ( ( 10 - DATEPART(dw,monthdate) ) % 7 ) + 7 , monthdate ) AS second_tuesday_of_month FROM monthdates

 

4.5.2.4. PostgreSQL

PostgreSQL gives 0 for Sunday, so you must add 1. Also, the output from EXtrACT is a floating-point number, so you must CAST it before you attempt modular arithmetic:

SELECT monthdate AS first_day_of_month , monthdate + ((10 - CAST(EXTRACT(dow FROM monthdate) + 1 AS INT)) % 7) + 7 AS second_tuesday_of_month FROM monthdates

Here are the results:

first_day_of_month second_tuesday_of_month 2007-04-01 2007-04-10 2007-05-01 2007-05-08 2007-06-01 2007-06-12

 

4.5.3. Hacking the Hack: The Last Thursday of the Month

You can use a similar technique to calculate the last Thursday of the month. Just find the first Thursday of next month and subtract seven days.

The formula for the offset for the first Thursday of the month beginning with monthdate is:

(12-DAYOFWEEK(monthdate) ) % 7

Subtract from 12 because Thursday is represented by 5 and 5 + 7 = 12.

The query to get the first day of next month is:

mysql> SELECT monthdate AS first_day_of_month -> ,DATE_ADD(monthdate,INTERVAL 1 MONTH) -> AS first_day_of_next_month -> FROM monthdates; +--------------------+-------------------------+ | first_day_of_month | first_day_of_next_month | +--------------------+-------------------------+ | 2007-04-01 | 2007-05-01 | | 2007-05-01 | 2007-06-01 | | 2007-06-01 | 2007-07-01 | +--------------------+-------------------------+

You can use this result to find the first Thursday of next month and subtract 7 to get the last Thursday of this month:

mysql> SELECT first_day_of_month -> ,DATE_ADD(first_day_of_next_month -> ,INTERVAL -> ((12 - DAYOFWEEK(first_day_of_next_month)) % 7) -> - 7 DAY) AS last_thursday_of_month -> FROM -> (SELECT monthdate AS first_day_of_month -> ,DATE_ADD(monthdate,INTERVAL 1 MONTH) -> AS first_day_of_next_month -> FROM monthdates) t; +--------------------+------------------------+ | first_day_of_month | last_thursday_of_month | +--------------------+------------------------+ | 2007-04-01 | 2007-04-26 | | 2007-05-01 | 2007-05-31 | | 2007-06-01 | 2007-06-28 | +--------------------+------------------------+

 

4.5.4. See Also

Rudy Limeback

Related

Категории