Finding the first or second Monday in a month

Syam asked a while ago how we could find something like the first Monday or third Saturday in a month. Well Syam, it’s taken me a while to respond but here we go.

For this example we’ll use sysdate as input but any Oracle date will work. You can also substitute any other day of the week for Monday.

The first day of the month is probably a good place to start:

SQL> select sysdate from dual;

SYSDATE
---------
18-JUL-06

SQL> select trunc(sysdate, 'MONTH') FROM DUAL;

TRUNC(SYS
---------
01-JUL-06

Now that we’ve got that we can find the first Monday with the next_day function. Of course we need to remember the next_day function looks for the next named day after the date provided so we subtract 1 day from the date in case the first is a Monday.

SQL> select next_day(trunc(sysdate, 'MONTH')-1, 'Monday') from dual;

NEXT_DAY(
---------
03-JUL-06

Now that we have the first Monday of the month we can add 7 days to find the second Monday or 14 to find the third.

SQL> select next_day(trunc(sysdate, 'MONTH')-1, 'Monday')+7 FROM dual;

NEXT_DAY(
---------
10-JUL-06

SQL> select next_day(trunc(sysdate, 'MONTH')-1, 'Monday')+14 FROM dual;

NEXT_DAY(
---------
17-JUL-06

So from here you can change the day you’re looking for or the week number you want it in.

oracle, date functions, sql, database, database development, pl/sql

6 thoughts on “Finding the first or second Monday in a month”

  1. Hi, the examples are very nice.
    Though you are maintaining “Incoming searches for this post”, its is so much of helpful for those practicing

  2. The below queries will help you to fetch the 2nd monday and 4th monday of a month.

    select NEXT_DAY(TRUNC(TO_DATE (sysdate),’MONTH’),’MON’) + 7 from dual;
    select NEXT_DAY(TRUNC(TO_DATE (sysdate),’MONTH’),’MON’) + 21 from dual;

    Instead of sysdate you can specify any date of that month which you require.

Leave a Reply

Your email address will not be published. Required fields are marked *