Generating Reports with SQL Date Handling - The Formula (Page 4 of 5 )

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:

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

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

A Tuesday, the eighth is the second Tuesday.

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

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

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

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

A 1st

B wkday

C 10–B

D C mod 7

E D+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:

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

1=Sunday ... 7=Saturday.

Subtract this number from 10 to get C:

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

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.

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.

Divide by 7 and keep the remainder to get D.

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.

Add 7 to get E.

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

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.

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

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

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

PostgreSQL. PostgreSQL gives 0 for Sunday, so you must add 1. Also, the output fromEXTRACTis a floating-point number, so you mustCASTit 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