Generating Reports with SQL Date Handling - HACK 23 Second Tuesday of the Month
(Page 3 of 5 )
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.
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 asNLS_TERRITORYin Oracle andDATEFIRSTin SQL Server. MySQL offersWEEKDAY(date), which returns 1 (Monday) through 7 (Sunday), as well asDAYOFWEEK(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.
Next: The Formula >>
More SQL Articles
More By O'Reilly Media
|
 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.
|
|
