I have a date column like so:
| DATE |
|---|
| 12/16/2024 |
| 12/12/2024 |
| 01/20/2025 |
| 02/12/2025 |
| 12/10/2025 |
I am looking to create another column that can be used as an identifier for the current month, next month, third month, or other. But it needs to keep the year in mind. For example, say the todays date is 12/01/2024, the end result would be as follows:
| DATE | THREE_MONTHS |
|---|---|
| 12/16/2024 | Current Month |
| 12/12/2024 | Current Month |
| 01/20/2025 | Next Month |
| 02/12/2025 | Third Month |
| 12/10/2025 | Other |
| 01/01/2024 | Other |
Everything I have found so far only uses the month itself ignoring the year. Is there a way to do this where I don't have to separately compare the month and year, so I don't have to write special code for if the current month is November, December, since the year changes for the Next Month and Third Month.
I hope that makes sense!
You can use FLOOR, MONTHS_BETWEEN and SYSDATE to find the number of whole months difference between the start of the current month and the date value and then wrap it in a CASE expression to get your textual output:
SELECT date_column,
CASE FLOOR(MONTHS_BETWEEN(date_column, TRUNC(SYSDATE, 'MM')))
WHEN 0 THEN 'Current Month'
WHEN 1 THEN 'Next Month'
WHEN 2 THEN 'Third Month'
ELSE 'Other'
END AS month
FROM table_name;
Which, for the sample data:
CREATE TABLE table_name (date_column) AS
SELECT DATE '2025-02-04' FROM DUAL UNION ALL
SELECT DATE '2025-02-28' FROM DUAL UNION ALL
SELECT DATE '2025-03-01' FROM DUAL UNION ALL
SELECT DATE '2025-04-15' FROM DUAL UNION ALL
SELECT DATE '2025-01-31' FROM DUAL UNION ALL
SELECT DATE '2026-02-04' FROM DUAL;
Outputs:
| DATE_COLUMN | MONTH |
|---|---|
| 2025-02-04 00:00:00 | Current Month |
| 2025-02-28 00:00:00 | Current Month |
| 2025-03-01 00:00:00 | Next Month |
| 2025-04-15 00:00:00 | Third Month |
| 2025-01-31 00:00:00 | Other |
| 2026-02-04 00:00:00 | Other |