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 '20070401'); INSERT INTO monthdates(monthdate) VALUES (DATE '20070501'); INSERT INTO monthdates(monthdate) VALUES (DATE '20070601');
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. DayofWeek Function
Although standard SQL does not provide a function to give the day of the week for any date, most database systems do. Table 45 shows some of the functions that can accomplish this.
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.

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:
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 46.
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 46 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:
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 floatingpoint 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 20070401 20070410 20070501 20070508 20070601 20070612
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:
(12DAYOFWEEK(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  +++  20070401  20070501   20070501  20070601   20070601  20070701  +++
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  +++  20070401  20070426   20070501  20070531   20070601  20070628  +++
4.5.4. See Also
Rudy Limeback
SQL Fundamentals
Joins, Unions, and Views
Text Handling
Date Handling
Number Crunching
Online Applications
Organizing Data
Storing Small Amounts of Data
Locking and Performance
Reporting
Users and Administration
Wider Access
Index