admin管理员组文章数量:1290991
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 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?
Share Improve this question asked Feb 13 at 19:24 user430997user430997 3851 silver badge8 bronze badges 2- Column data type? – jarlh Commented Feb 13 at 19:25
- 2 Provide a complete minimal reproducible example, i.e. specify both sample table data and the expected result. – jarlh Commented Feb 13 at 19:27
2 Answers
Reset to default 3I 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;
Fiddle
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 |
As far as I know, EXTRACT is not supported in 2019 or 2022. (Check the MS SQL Server Docs to be sure.) You can use DATEPART() to get day, month, quarter, year from a DATE, DATETIME data type.
本文标签： db2Adding a hyphen between dates in SQLStack Overflow
版权声明：本文标题：db2 - Adding a hyphen between dates in SQL - Stack Overflow 内容由网友自发贡献，该文观点仅代表作者本人， 转载请联系作者并注明出处：http://www.betaflare.com/web/1741508728a2382478.html， 本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容，一经查实，本站将立刻删除。
发表评论