Q
Problem solve Get help with specific problems with your technologies, process and projects.

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.

This was last published in February 2007

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close