Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Jj.

Asked: September 03, 2022 - 2:36 am UTC

Last updated: September 06, 2022 - 5:35 am UTC

Version: Oracle19c

Viewed 1000+ times

You Asked

How to calculate weekly counts with in a year. I want the week to start from Monday to Sunday. Thanks.

and Connor said...

IW format mask gives a week commencing monday, so just a trunc will do

SQL> select to_char(trunc(sysdate,'IW'),'DY') from dual;

TO_CHAR(TRUN
------------
MON

SQL> select trunc(created,'IW') wk, count(*)
  2  from   user_objects
  3  group by trunc(created,'IW')
  4  order by 1;

WK          COUNT(*)
--------- ----------
21-OCT-19          1
18-NOV-19          6
09-DEC-19          1
24-FEB-20          2
23-MAR-20          1
30-MAR-20          2
06-APR-20          3
13-APR-20          2
20-APR-20          2
27-APR-20          3
04-MAY-20          4
11-MAY-20          6

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library