I have a column (somecolumn) in a table (sometable) in the following format (DATE): 2025-01-01 00:00:00
Using SQL, I want to make the 2 following columns:
1) Extract the month number based on April-April Calendar, i.e. April =1, May =2, ...
2) Identify the year based on the April-April Calendar, i.e. Jan 1st 2025 is in the 2024-2025 year.
The first one is easy to do manually:
SELECT
CASE
WHEN EXTRACT(MONTH FROM somedate) = 4 THEN 1
WHEN EXTRACT(MONTH FROM somedate) = 5 THEN 2
WHEN EXTRACT(MONTH FROM somedate) = 6 THEN 3
WHEN EXTRACT(MONTH FROM somedate) = 7 THEN 4
WHEN EXTRACT(MONTH FROM somedate) = 8 THEN 5
WHEN EXTRACT(MONTH FROM somedate) = 9 THEN 6
WHEN EXTRACT(MONTH FROM somedate) = 10 THEN 7
WHEN EXTRACT(MONTH FROM somedate) = 11 THEN 8
WHEN EXTRACT(MONTH FROM somedate) = 12 THEN 9
WHEN EXTRACT(MONTH FROM somedate) = 1 THEN 10
WHEN EXTRACT(MONTH FROM somedate) = 2 THEN 11
WHEN EXTRACT(MONTH FROM somedate) = 3 THEN 12
END AS new_month
FROM sometable;
The second one is more confusing. I tried to think of the logic by breaking months into (Jan-March and otherwise):
CASE
WHEN EXTRACT(MONTH FROM somecolumn) <= 3
THEN (EXTRACT(YEAR FROM somecolumn) - 1) || '-' || EXTRACT(YEAR FROM somecolumn)
ELSE EXTRACT(YEAR FROM somecolumn) || '-' || (EXTRACT(YEAR FROM somecolumn) + 1)
END AS new_year
FROM sometable;
Is this the correct logic?
I think what you are proposing works already as expected.
Small change is you need to cast the year to a VARCHAR as you are concatenating a string (-) in the new_year column.
Another way to find the new_month is to use modulo to shift the year start to April by adding 8 to the month number and then dividing by 12 like so
(MONTH(test_col) + 8) % 12 + 1 AS new_month
And then you can use it to calculate the new_year like you already did
Sample query
SELECT
test_col,(MONTH(test_col) + 8) % 12 + 1 AS new_month,
CASE
WHEN MONTH(test_col) <=3 THEN
VARCHAR(YEAR(test_col) - 1) || '-' || VARCHAR(YEAR(test_col))
ELSE VARCHAR(YEAR(test_col)) || '-' || VARCHAR(YEAR(test_col) + 1)
END AS new_year
FROM test;
Outputs
| TEST_COL | NEW_MONTH | NEW_YEAR |
|---|---|---|
| 2024-01-15 | 10 | 2023-2024 |
| 2024-02-21 | 11 | 2023-2024 |
| 2024-03-10 | 12 | 2023-2024 |
| 2024-04-05 | 1 | 2024-2025 |
| 2024-05-18 | 2 | 2024-2025 |
| 2024-06-25 | 3 | 2024-2025 |
| 2024-07-04 | 4 | 2024-2025 |
| 2024-08-11 | 5 | 2024-2025 |
| 2024-09-20 | 6 | 2024-2025 |
| 2024-10-03 | 7 | 2024-2025 |
| 2024-11-26 | 8 | 2024-2025 |
| 2024-12-17 | 9 | 2024-2025 |
| 2025-01-01 | 10 | 2024-2025 |
| 2025-02-21 | 11 | 2024-2025 |
| 2025-03-10 | 12 | 2024-2025 |
| 2025-04-05 | 1 | 2025-2026 |