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:

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

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:

1. Find B, the day of the week of the first day of the month, using:
   
     1=Sunday ... 7=Saturday.

2. 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.

3. 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.

4. 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.

5. 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

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

Next: Hacking the Hack: The Last Thursday of the Month >>
 

More SQL Articles
More By O'Reilly Media

Comments On: Generating Reports with SQL Date Handling

· This article is an excerpt from the book "SQL Hacks," published by O'Reilly. We hope...   >>> Post your comment now!

Buy this book...

This article is excerpted from chapter four of the book SQL Hacks, written by Andrew Cumming and Gordon Russell (O'Reilly, 2006; ISBN: 0596527993). Check it out today at your favorite bookstore. Buy this book now.