I have a table in sql. There is a date column (e.g. 2005-04-28).

I want to find out which week number (i.e. 1 to 52 or 1 to 53 depending on the year) each date belongs to based on an april-april calendar.

CREATE TABLE some_table (
    id INT,
    my_date TIMESTAMP
);

INSERT INTO some_table  VALUES
(1, '2023-03-15 00:00:00'), 
(2, '2023-04-01 00:00:00'),
(3, '2023-04-10 00:00:00'),
(3, '2023-04-10 00:00:00'),
(4, '2023-05-15 00:00:00'),
(5, '2023-08-01 00:00:00'),
(6, '2023-12-25 00:00:00'),
(7, '2024-01-15 00:00:00'),
(8, '2024-03-31 00:00:00'),
(9, '2024-04-01 00:00:00'),
(10, '2022-12-31 00:00:00');

Here is what I am trying based on manual logic (based on this how to get week number for a date based on month from the date in sql server):

SELECT 
    my_date,
    CASE 
        WHEN EXTRACT(MONTH FROM my_date) < 4 OR 
             (EXTRACT(MONTH FROM my_date) = 4 AND EXTRACT(DAY FROM my_date) < 1)
        THEN 
            CEIL((my_date - DATE((EXTRACT(YEAR FROM my_date) - 1) || '-04-01')) / 7) + 1
        ELSE
            CEIL((my_date - DATE(EXTRACT(YEAR FROM my_date) || '-04-01')) / 7) + 1
    END AS april_to_april_week
FROM some_table;

The final result should look like this:

CREATE TABLE expected_results (
    id INT,
    my_date TIMESTAMP,
    april_to_april_week INT
);

INSERT INTO expected_results (id, my_date, april_to_april_week) VALUES
(1, '2023-03-15 00:00:00', 51),
(2, '2023-04-01 00:00:00', 1),
(3, '2023-04-10 00:00:00', 2),
(3, '2023-04-10 00:00:00', 2),
(4, '2023-05-15 00:00:00', 7),
(5, '2023-08-01 00:00:00', 18),
(6, '2023-12-25 00:00:00', 39),
(7, '2024-01-15 00:00:00', 42),
(8, '2024-03-31 00:00:00', 52),
(9, '2024-04-01 00:00:00', 1),
(10, '2022-12-31 00:00:00', 40);

I am still trying to verify my manual logic. Can someone help me please?

I have a table in sql. There is a date column (e.g. 2005-04-28).

I want to find out which week number (i.e. 1 to 52 or 1 to 53 depending on the year) each date belongs to based on an april-april calendar.

CREATE TABLE some_table (
    id INT,
    my_date TIMESTAMP
);

INSERT INTO some_table  VALUES
(1, '2023-03-15 00:00:00'), 
(2, '2023-04-01 00:00:00'),
(3, '2023-04-10 00:00:00'),
(3, '2023-04-10 00:00:00'),
(4, '2023-05-15 00:00:00'),
(5, '2023-08-01 00:00:00'),
(6, '2023-12-25 00:00:00'),
(7, '2024-01-15 00:00:00'),
(8, '2024-03-31 00:00:00'),
(9, '2024-04-01 00:00:00'),
(10, '2022-12-31 00:00:00');

Here is what I am trying based on manual logic (based on this how to get week number for a date based on month from the date in sql server):

SELECT 
    my_date,
    CASE 
        WHEN EXTRACT(MONTH FROM my_date) < 4 OR 
             (EXTRACT(MONTH FROM my_date) = 4 AND EXTRACT(DAY FROM my_date) < 1)
        THEN 
            CEIL((my_date - DATE((EXTRACT(YEAR FROM my_date) - 1) || '-04-01')) / 7) + 1
        ELSE
            CEIL((my_date - DATE(EXTRACT(YEAR FROM my_date) || '-04-01')) / 7) + 1
    END AS april_to_april_week
FROM some_table;

The final result should look like this:

CREATE TABLE expected_results (
    id INT,
    my_date TIMESTAMP,
    april_to_april_week INT
);

INSERT INTO expected_results (id, my_date, april_to_april_week) VALUES
(1, '2023-03-15 00:00:00', 51),
(2, '2023-04-01 00:00:00', 1),
(3, '2023-04-10 00:00:00', 2),
(3, '2023-04-10 00:00:00', 2),
(4, '2023-05-15 00:00:00', 7),
(5, '2023-08-01 00:00:00', 18),
(6, '2023-12-25 00:00:00', 39),
(7, '2024-01-15 00:00:00', 42),
(8, '2024-03-31 00:00:00', 52),
(9, '2024-04-01 00:00:00', 1),
(10, '2022-12-31 00:00:00', 40);

I am still trying to verify my manual logic. Can someone help me please?

• sql
• db2

3 Answers 3

You can subtract the days to 1-Apr from the date, and then you should be able to use the WEEK function to get it

 SELECT *
  , week(my_date - (dayofyear(DATE(YEAR(my_date) || '-04-01'))-1) DAYS) as WeekNo
FROM expected_results;

With your query my_date - DATE(EXTRACT(YEAR FROM my_date) || '-04-01') not is day count between this dates.
For example

'2023-05-15 11:22:33..44556677'-'2023-04-01 00:00:00.000000'=114112233.445566
      ->1 month 14 days 11 hour 22 min 33 sec and so on(.445566)  

It doesn't make sense to calculate this difference and divide by 7.

See example

select * 
  ,(days_between(my_date
    ,add_months(date_trunc('month',my_date),-((month(my_date)-4+12)%12))
    )+7)/7 wn
from some_table;

To compare query output and your expected_result (WN and APRIL_TO_APRIL_WEEK)

Look at the difference. On the one hand, they are associated with a leap year, when there are 366 days in a year -> 52 weeks and 1 more day (week). It might be worth looking at the rules for calculating the week number, for example ISO_week.

fiddle

tests

I see in your logic is calculate the week number in April to April 4 each date
so you can remove the condition checking if the date was before April 1 by extracting month and day but it was overly complex and remove 'ciel'. Can you test this

SELECT 
    my_date,
    CASE 
        WHEN my_date < DATE(EXTRACT(YEAR FROM my_date) || '-04-01')
        THEN FLOOR((my_date - DATE(EXTRACT(YEAR FROM my_date) - 1 || '-04-01'))::numeric / 7) + 1
        ELSE FLOOR((my_date - DATE(EXTRACT(YEAR FROM my_date) || '-04-01'))::numeric / 7) + 1
    END AS april_to_april_week
FROM some_table;

Condition check date before April of this year and use FLOOR giving only completed week matches and casting to numeric. If you want to sort the results you can use

ORDER BY my_date