ADD_MONTHS_LAST() does not work correctly ?

[ssurampally]

I am trying to get the last day in the previous month, I am using the using below SQL, it is giving the today's date in last month, but not the last day of the month.

SELECT ADD_MONTHS_LAST (CURRENT_DATE, -1) FROM DUMMY ;

I am not finding any difference of using, ADD_MONTHS() and ADD_MONTHS_LAST() with CURRENT_DATE.

the official documentation explaining an example, which has the last day in the date, so it does not explain behavior working,

do you know why this is? or I am missing something?

[former_member213277 · Accepted solution]

Hi Sreekanth,

I tested it and found that, ADD_MONTHS_LAST will provide last day of the month(next month/previous month) only if the input date is last day of the month, else ADD_MONTHS_LAST will behave same as ADD_MONTHS

Regards,

Nag

[ssurampally · Accepted solution]

Ya thanks, it does not work for CURRENT_DATE(), input has to the last day of the month. I just read the documentation again, noticed that, behavior is correct.

[TuncayKaraca · Accepted solution]

Sreekanth - For your requirement

1. You need to find the previous month, so here you can use CURRENT_DATE as a reference if you mean actual / current date
2. Then you need to find the last day of the previous month