This give a clear explanation on what you need to do:
Finding the first or second Monday in a month[
^].
You can expand it to find the third and fourth 'Monday' in the month. When looking for the fourth make sure you do not go into the next month.
Replace 'Monday' with the other days of the week, for Sunday, etc.
The example uses
sysdate
which you can replace with the date from your data column.
Test from link as per OP request:
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.