Converting between calendars in SQL

Question

I am trying to convert calendar dates (jan-jan) into dates based on the (april-april) calendar.

Basically what I am trying to do is this:

• Start with a date in this format full_date = 2020-12-12
• Identify the week number relative to the january calendar
• Identify the year, month, week number (i.e. relative to the april-april calendar)
• Only select rows where column 1 is a number or decimal number

Normally I work with Python and this would not be too much of a problem. But I am trying to do this with SQL.

I tried to piece together this code:

SELECT 
    full_date,    
EXTRACT(WEEK FROM full_date) AS jan_calendar_week,

    CASE 
        WHEN EXTRACT(MONTH FROM full_date) <= 3 THEN EXTRACT(MONTH FROM full_date) + 9
        ELSE EXTRACT(MONTH FROM full_date) - 3
    END AS april_calendar_month,
    

    CASE 
        WHEN EXTRACT(MONTH FROM full_date) <= 3 
        THEN (EXTRACT(YEAR FROM full_date) - 1) || '-' || EXTRACT(YEAR FROM full_date)
        ELSE EXTRACT(YEAR FROM full_date) || '-' || (EXTRACT(YEAR FROM full_date) + 1)
    END AS april_calendar_year,

    CASE 
        WHEN EXTRACT(MONTH FROM full_date) <= 3 
        THEN CEIL((full_date - DATE(EXTRACT(YEAR FROM full_date) - 1 || '-04-01')) / 7.0)
        ELSE CEIL((full_date - DATE(EXTRACT(YEAR FROM full_date) || '-04-01')) / 7.0)
    END AS april_calendar_week_number
FROM t;

WHERE regexp_like(col1, '^[0-9]+$|^[0-9]+\.[0-9]+$');

Is this the correct logic? Are there easier ways to do this using pre-defined functions?

---

CREATE TABLE t(
    full_date DATE,
    col1 VARCHAR(50)
);

INSERT INTO t (full_date, col1) VALUES
    ('2023-03-31', '100.50'), 
    ('2023-04-01', '200.75'),  
    
    ('2023-01-15', '300.25'),  
    ('2023-07-01', '400.00'),  
    
    ('2023-05-01', 'ABC'),     
    ('2023-06-01', '500');