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.
how to get the last saturdays of every year using sql queries.
Hi, the examples are very nice.
Though you are maintaining “Incoming searches for this post”, its is so much of helpful for those practicing
great information we are getting through this site.it is better if we get these type of post to our mail
how to find all the first day of a months in a year using sql..
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.