Oracle SQL: How to add day intervals to dates?

March 15, 2023

I want to add day intervals to a list of dates in Oracle SQL, to the next week’s Monday. However, I am getting a very weird error when I use the suggested date + 1 kind of interval, wherein only one day is getting add up as expected, i.e., Wednesday. What am I doing wrong here? Is this a bug in Oracle SQL Developer?

WITH data AS (
    SELECT '29-JAN-23' dt FROM dual UNION
    SELECT '30-JAN-23' dt FROM dual UNION
    SELECT '31-JAN-23' dt FROM dual UNION
    SELECT '01-FEB-23' dt FROM dual UNION
    SELECT '02-FEB-23' dt FROM dual UNION
    SELECT '03-FEB-23' dt FROM dual UNION
    SELECT '04-FEB-23' dt FROM dual UNION
    SELECT '05-FEB-23' dt FROM dual UNION
    SELECT '06-FEB-23' dt FROM dual UNION
    SELECT '07-FEB-23' dt FROM dual UNION
    SELECT '08-FEB-23' dt FROM dual UNION
    SELECT '09-FEB-23' dt FROM dual
)

SELECT
    TO_DATE(dt) dt,
    TO_CHAR(TO_DATE(dt),'DAY') dt_day,
    CASE
        WHEN TO_CHAR(TO_DATE(dt),'DAY') = 'MONDAY'      THEN TO_DATE(dt)
        WHEN TO_CHAR(TO_DATE(dt),'DAY') = 'TUSEDAY'     THEN TO_DATE(dt) + 6
        WHEN TO_CHAR(TO_DATE(dt),'DAY') = 'WEDNESDAY'   THEN TO_DATE(dt) + 5
        WHEN TO_CHAR(TO_DATE(dt),'DAY') = 'THURSDAY'    THEN TO_DATE(dt) + 4
        WHEN TO_CHAR(TO_DATE(dt),'DAY') = 'FRIDAY'      THEN TO_DATE(dt) + 3
        WHEN TO_CHAR(TO_DATE(dt),'DAY') = 'SATURDAY'    THEN TO_DATE(dt) + 2
        WHEN TO_CHAR(TO_DATE(dt),'DAY') = 'SUNDAY'      THEN TO_DATE(dt) + 1
    END new_dt
FROM data
ORDER BY TO_DATE(dt)
;

Output

| DT        | DT_DAY    | NEW_DT    |
|-----------|-----------|-----------|
| 29-JAN-23 | SUNDAY    |           |
| 30-JAN-23 | MONDAY    |           |
| 31-JAN-23 | TUESDAY   |           |
| 01-FEB-23 | WEDNESDAY | 06-FEB-23 |
| 02-FEB-23 | THURSDAY  |           |
| 03-FEB-23 | FRIDAY    |           |
| 04-FEB-23 | SATURDAY  |           |
| 05-FEB-23 | SUNDAY    |           |
| 06-FEB-23 | MONDAY    |           |
| 07-FEB-23 | TUESDAY   |           |
| 08-FEB-23 | WEDNESDAY | 13-FEB-23 |
| 09-FEB-23 | THURSDAY  |           |

>Solution :

A couple of things: use format mask for to_date, then keep in mind that ‘DAY’ returns a string that is filled with blanks up to a certain length.
Therefore:

WITH data AS (
SELECT '29-JAN-23' dt FROM dual UNION
SELECT '30-JAN-23' dt FROM dual UNION
SELECT '31-JAN-23' dt FROM dual UNION
SELECT '01-FEB-23' dt FROM dual UNION
SELECT '02-FEB-23' dt FROM dual UNION
SELECT '03-FEB-23' dt FROM dual UNION
SELECT '04-FEB-23' dt FROM dual UNION
SELECT '05-FEB-23' dt FROM dual UNION
SELECT '06-FEB-23' dt FROM dual UNION
SELECT '07-FEB-23' dt FROM dual UNION
SELECT '08-FEB-23' dt FROM dual UNION
SELECT '09-FEB-23' dt FROM dual

)

SELECT
    TO_DATE(dt) dt,
    TO_CHAR(TO_DATE(dt,'dd-mon-rr'),'DAY') dt_day,
    CASE
        WHEN trim(TO_CHAR(TO_DATE(dt,'dd-mon-rr'),'DAY')) = 'MONDAY'      THEN TO_DATE(dt,'dd-mon-rr')
        WHEN trim(TO_CHAR(TO_DATE(dt,'dd-mon-rr'),'DAY')) = 'TUSEDAY'     THEN TO_DATE(dt,'dd-mon-rr') + 6
        WHEN trim(TO_CHAR(TO_DATE(dt,'dd-mon-rr'),'DAY')) = 'WEDNESDAY'   THEN TO_DATE(dt,'dd-mon-rr') + 5
        WHEN trim(TO_CHAR(TO_DATE(dt,'dd-mon-rr'),'DAY')) = 'THURSDAY'    THEN TO_DATE(dt,'dd-mon-rr') + 4
        WHEN trim(TO_CHAR(TO_DATE(dt,'dd-mon-rr'),'DAY')) = 'FRIDAY'      THEN TO_DATE(dt,'dd-mon-rr') + 3
        WHEN trim(TO_CHAR(TO_DATE(dt,'dd-mon-rr'),'DAY')) = 'SATURDAY'    THEN TO_DATE(dt,'dd-mon-rr') + 2
        WHEN trim(TO_CHAR(TO_DATE(dt,'dd-mon-rr'),'DAY')) = 'SUNDAY'      THEN TO_DATE(dt,'dd-mon-rr') + 1
    END new_dt
FROM data
ORDER BY TO_DATE(dt,'dd-mon-rr')
;