Months that ends with 29,30,31 ORACLE

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

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

fiddle