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

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.

Close