CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Cagri Tacyildiz
Cagri Tacyildiz

Reputation: 17610

Get number of weeks of specific year and month

In Oracle SQL, I want to get the number of weeks of specific month and year. For example: Input will be 2019 as year 8 as month it includes 5 weeks in it.

PROCEDURE SP_WEEKS(P_REFCURSOR OUT SYS_REFCURSOR,P_YEAR IN NUMBER:=-1,P_MONTH IN NUMBER:=-1)  IS
  BEGIN
       OPEN P_REFCURSOR FOR

       --here will return table
  END SP_WEEKS

And result will be

ID  NAME
--  ----
1     1
2     2
3     3
4     4
5     5

ID and NAME is week number. How can I write this?

Thanks in advance

Upvotes: 0

Views: 129

Answers (1)

Aleksej
Aleksej

Reputation: 22949

If I understand well your need, you may try:

CREATE OR REPLACE PROCEDURE SP_WEEKS(P_REFCURSOR OUT SYS_REFCURSOR,P_YEAR IN NUMBER:=-1,P_MONTH IN NUMBER:=-1)  IS
BEGIN
     OPEN P_REFCURSOR FOR
     select level as ID, level as NAME
      from dual
      connect by add_months( date '0001-01-01', 12*P_YEAR + P_MONTH -1) + (level -1)*7
              <= last_day(add_months( date '0001-01-01', 12*P_YEAR + P_MONTH -1));
END SP_WEEKS;

For example:

SQL> var result refcursor
SQL> begin
  2    SP_WEEKS(:result, 2019, 8);
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> print :result

        ID       NAME
---------- ----------
         1          1
         2          2
         3          3
         4          4
         5          5

This uses a commonly used technique to generate rows by starting with the first day of the given month and adding 7 days for each iteration (LEVEL); the CONNECT BY condition stops the iteration when the resulting date is not in the month.

Upvotes: 1

Related Questions