RATE THIS ANSWER
+3
Click to Vote:
3
0
Last Answered:
Feb 28 2008 5:06 PM GMT
by Randym
You can use a combination of oracle functions within one select statement to get your answer. The following code seems to work:
select (next_day(last_day(sysdate),'SAT') - next_day(last_day(add_months(sysdate,-1)),'SAT')) / 7 from dual;
Sysdate gives the current date; but you could replace sysdate with any date
The concept is to get the number of days between the first saturday of the month and the first saturday of the next month. Then divide by 7.
The first Saturday of NEXT month can be found by getting the last day of the current month <last_day(sysdate),> and then getting the next Saturday <next_day(last_day(sysdate),'SAT')>
To get the first Saturday of the CURRENT month, we first need to get the date one month back <add_months(sysdate,-1)>. Then we can get the last day of that month <last_day(add_months(sysdate,-1))>. Finally, we can get the first Saturday of the current month (which is the next Saturday after the last day of the previous month) <next_day(last_day(add_months(sysdate,-1)),'SAT')) >
Subtract those two dates and divide by 7 and you have your answer.