How to retrieve the week number of the year starting at 1 Jan?

So I have a shell script which returns me the week number using an SQL query :

select TO_CHAR(TO_DATE('01/01/2022','DD/MM/YYYY'),'fmWW') as WEEK from dual;

This query returns 1. (Starting at 1st Jan)

Python code: I have tried the following however it returns 52 rather than 1 :

week=datetime.date(2022,1,1).isocalendar()[1]

Week starting from Mon-Sun

Is there another way in which I can get the week number of year based on starting at 1st Jan so I get 1 for 01/01/2022?

Required output:

Week ending Jan 2 2022 = 1
Week ending Jan 9 2022 = 2
Week ending Jan 16 2022 = 3

Answer

Is there another way in which I can get the week number of year based on starting at 1st Jan so I get 1 for 01/01/2022?

You can use, for a given date dt, as you said in the question:

TO_CHAR(dt, 'fmWW')

or can calculate it using:

FLOOR((dt - TRUNC(dt, 'YY'))/7)+1

or, to match the python code, can get the ISO week using:

TO_CHAR(dt, 'fmIW')

Or, if you want to start counting the week from the 1st January and change weeks on a Monday then:

FLOOR((dt - TRUNC(TRUNC(dt, 'YY'), 'IW'))/7)+1

Then the query:

WITH sample_dates (dt) AS (
  SELECT DATE '2021-12-30' + LEVEL - 1 FROM DUAL CONNECT BY LEVEL <= 40
)
SELECT dt,
       TO_CHAR(dt, 'fmWW') AS week,
       TO_CHAR(dt, 'fmIW') AS isoweek,
       FLOOR((dt - TRUNC(dt, 'YY'))/7)+1 AS weekfromyearstart,
       FLOOR((dt - TRUNC(TRUNC(dt, 'YY'), 'IW'))/7)+1 AS montosunweekfromyearstart
FROM   sample_dates

Outputs:

DT	WEEK	ISOWEEK	WEEKFROMYEARSTART	MONTOSUNWEEKFROMYEARSTART
2021-12-30	52	52	52	53
2021-12-31	53	52	53	53
2022-01-01	1	52	1	1
2022-01-02	1	52	1	1
2022-01-03	1	1	1	2
2022-01-04	1	1	1	2
2022-01-05	1	1	1	2
2022-01-06	1	1	1	2
2022-01-07	1	1	1	2
2022-01-08	2	1	2	2
2022-01-09	2	1	2	2
2022-01-10	2	2	2	3
2022-01-11	2	2	2	3
2022-01-12	2	2	2	3
2022-01-13	2	2	2	3
2022-01-14	2	2	2	3
2022-01-15	3	2	3	3
2022-01-16	3	2	3	3
2022-01-17	3	3	3	4
2022-01-18	3	3	3	4
2022-01-19	3	3	3	4
2022-01-20	3	3	3	4
2022-01-21	3	3	3	4
2022-01-22	4	3	4	4
2022-01-23	4	3	4	4
2022-01-24	4	4	4	5
2022-01-25	4	4	4	5
2022-01-26	4	4	4	5
2022-01-27	4	4	4	5
2022-01-28	4	4	4	5
2022-01-29	5	4	5	5
2022-01-30	5	4	5	5
2022-01-31	5	5	5	6
2022-02-01	5	5	5	6
2022-02-02	5	5	5	6
2022-02-03	5	5	5	6
2022-02-04	5	5	5	6
2022-02-05	6	5	6	6
2022-02-06	6	5	6	6
2022-02-07	6	6	6	7

db<>fiddle here

---

If you want a Python function then:

import datetime

def week_from_year_start(dt: datetime.date) -> int:
     year_start = dt.replace(month=1, day=1)
     return int((dt - year_start).days/7) + 1

or:

def mon_sun_week_from_year_start(dt: datetime.date) -> int:
     year_start = dt.replace(month=1, day=1)
     week_start = year_start - datetime.timedelta(days=year_start.weekday())
     return int((dt - week_start).days/7) + 1