First or third Monday of the month?

Given a date that falls on Monday, how can I know if it is the first or third Monday of the month?

Given a date, say it falls on Monday, how can I know if it is the third Monday or first Monday of the month? I would like to know this so that I can determine if the date falls in my list of holidays or not. Thanks in advance.

Please allow me to suggest that there is a much easier way. Rather than storing a list of holidays with complicated formulae like first Monday and so on, and then trying to figure out if a given date matches any of the stored holidays, consider building a calendar table.

For example:

caldate     wkday  holiday     office_open
2007-04-04    4     NULL         1
2007-04-05    5     NULL         1
2007-04-06    6    Good Friday   0
2007-04-07    7     NULL         0
2007-04-08    1     NULL         0
2007-04-09    2     NULL         0
2007-04-10    3     NULL         1
2007-04-11    4     NULL         1

In this example, Friday April 6, 2007 is a holiday (Good Friday), and the office is not open. The office is also not open on Saturday or Sunday, as usual, but neither is it open on the following Monday.

There are two advantages. Firstly, your SQL will be mean and lean, because all you need to do is join to the calendar table, or do a simple lookup query. The query to find the number of business days between two dates is trivial; just SUM the office_open column. Secondly, you will be able to record special events for your particular office, even if they aren't normal holidays.

How to create this calendar table? Manually. It takes only a few minutes every year to prepare the data. I like to use Excel because it's easy to use Edit>Fill>Series to populate the date column, as well as copy/paste for repeating data like the wkday values. Then you can easily forward the worksheet to HR or Accounting, to have them verify the accuracy of the holidays and office closings for your company before you load the data into the database.

Dig Deeper on Oracle and SQL