Q

Nth Saturday of the month

Using the date functions, can I find out whether a particular date is the 1st, 2nd, 3rd, 4th or 5th Saturday of a month? Our leave rules have changed recently and I have to update my system to reflect full working on 1st, 3rd and 5th Saturdays, and full leave on 2nd and 4th Saturdays.

Using the date functions, can I find out whether a particular date is the 1st, 2nd, 3rd, 4th or 5th Saturday of...

a month? Our leave rules have changed recently and I have to update my system to reflect full working on 1st, 3rd and 5th Saturdays, and full leave on 2nd and 4th Saturdays.

The right SQL solution to your question is going to depend on which database system you're using. Date functions, more than any other area of SQL, vary from one database to the next. So I'm going to offer you an approach instead, and trust that you can apply it to your particular database system, whatever it may be, based on how it works.

Let's assume that the query will always be given a valid Saturday date, so that we can simply convert that date to an integer between 1 and 5.

With that preamble, here's the approach:

  1. take the day of the month
  2. subtract 1
  3. divide by 7 and throw away the remainder
  4. add 1

Et voilà! We have a number between 1 and 5 that tells us whether the Saturday date is the 1st through 5th Saturday of the month.

Here's an example of a query which updates the 2nd and 4th Saturdays only, using Microsoft Access syntax:

update leave_rules 
   set full_leave = 'Y'
 where int((datepart("d",saturday_date)-1)/7)+1 
    in ( 2, 4 )

The Microsoft Access INT() function is a floor function which returns the greatest integer less than or equal to its argument. DATEPART() with "d" as first parameter extracts the day of month (many databases also have a DAY() function for this).

The really neat part about this approach is that it doesn't have to be a Saturday date; it works for any day of the week.

This was last published in February 2006

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.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close