Finding the “Floating” calendar dates:

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

To calculate the second Tuesday of the month, this depends on the day of the week of the first day of the month. But we need to know which month is this? Obviously, all we need to know are the month and year, but it’s easier if we start with a date as such. For testing purposes, the following table can be used.


Create table month_date (month_date date);
Insert into month_date values(date ‘2009-04-01’);
Insert into month_date values(date ‘2009-05-01’);
Insert into month_date values(date ‘2009-06-01’);
Insert into month_date values(date ‘2009-07-01’);

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

Day-of-week function:

Even though standard SQL doesn’t provide any function to give the day of the week for any date, most of the database systems do.

ORACLE provides a function:

TO_CHAR(DATE,’D’);

The essence of this function is that it will return a number between 0 and 6 or 1 and 7.Sometimes 0 or 1 is Sunday and 6 or 7 is Saturday, and sometime 0 or 1 is Monday and 6 or 7 is Sunday. This depends on how the NLS_TERRITORY parameter is set.

Note: We follow that 1 is Sunday and 7 is Saturday in our example.
-----

The Formula:

To convert the first day of the month into second Tuesday of the month simply involves manipulating the day of the week of the first day with in an arithmetic formula. Before we see the formula, we should know what happens when the first day of the month falls on each day of the week from Sunday through Saturday.

Lets see this:

1. A Sunday, the third is a Tuesday, so the tenth is the second Tuesday
2. A Monday, the second is 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 and fourteenth is the second Tuesday.
5. A Thursday, the sixth is the next Tuesday and thirteenth is the second Tuesday
6. A Friday, the fifth is the next Tuesday and the twelfth is the second Tuesday
7. A Saturday, the fourth is the next Tuesday and the eleventh is the second Tuesday

This gives us all the possibilities. So now we have to reduce these facts to formula.

A	B	C	D	E
1ST	WEEK DAY	10-B	C MOD 7	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 day using the range from 1 to 7.
The important data in the above table is the last column; which is the number of days to add to the date of the first day of the month.

So, the formula can be derived as follows.

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 10 because you want C to be positive for all inputs. 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 remainder to get D value.
D is also the offset for a Tuesday, and D in the range of 0 to 6. every day in the first week has an offset between 0 to 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 of 7-13.

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

Implementing this on ORACLE database:

SELECT TO_CHAR (month_date, 'dd-mon-yyyy') AS first_day_of_month,
TO_CHAR (( (month_date)
+ MOD ((10 - TO_CHAR ((month_date), 'd')), 7)
+ 7
),
'dd-mon-yyyy'
) AS second_tuesday_of_month
FROM month_date

OUTPUT
*******

FIRST_DAY_OF_MONTH	SECOND_TUESDAY_OF_MONTH
01-apr-2009	14-apr-2009
01-may-2009	12-may-2009
01-jun-2009	09-jun-2009
01-jul-2009	14-jul-2009

Hope this is useful for you guys.