convert into date format oracle there is an identical question in the continuation of this discussion.
I have a field c_day in the table my_table that accepts numeric values from 1 to 31. In this field. I need to add 210 days to today’s date, and insert the value from c_day from the released date dd.mm.yyyy. For example, we take today’s date 08.02.2023 and add 210 days to it, the date falls on September, and if c_day is 20, then the output should be 20.09.2023. But if c_dayis equal to 31, then of course the date should be set as 30.09.2023, because the last day of September is 30.
Now I settled on cases where September ends on the 30th, and the field takes values from 1 to 31. How can I write a condition in such cases so that it takes the last day of the month?
I tried this one, but it doesn’t work:
SELECT
C_DAY,
LEAST(
TO_DATE(TO_CHAR(TRUNC(SYSDATE) + 210, 'YYYY-MM-') || C_DAY, 'YYYY-MM-DD'),
CASE
WHEN C_DAY < TO_CHAR(LAST_DAY(TRUNC(SYSDATE) + 210), 'DD') THEN
last_day(TO_DATE(TO_CHAR(TRUNC(SYSDATE) + 210, 'YYYY-MM-') || C_DAY, 'YYYY-MM-DD'))
END
) as result
FROM MY_TABLE
ORDER BY 1
Advertisement
Answer
You can can do it without any string manipulation using:
SELECT C_DAY,
LEAST(
TRUNC(TRUNC(SYSDATE) + 210, 'MM') + C_DAY - 1,
LAST_DAY(TRUNC(SYSDATE) + 210)
) AS result
FROM MY_TABLE
ORDER BY c_day
Which, for the sample data:
CREATE TABLE my_table ( c_day ) AS SELECT LEVEL FROM DUAL CONNECT BY LEVEL <= 31;
Outputs:
| C_DAY | RESULT |
|---|---|
| 1 | 2023-09-01 00:00:00 |
| 2 | 2023-09-02 00:00:00 |
| 3 | 2023-09-03 00:00:00 |
| … | … |
| 28 | 2023-09-28 00:00:00 |
| 29 | 2023-09-29 00:00:00 |
| 30 | 2023-09-30 00:00:00 |
| 31 | 2023-09-30 00:00:00 |