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:
- take the day of the month
- subtract 1
- divide by 7 and throw away the remainder
- 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.
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading