Question

Using SELECT statements

Oracle, Oracle development, SELECT statement

Can we get the number of saturdays in any month using a SELECT statement(i.e without creating a table or function)? If so, can you give me the logic?

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.