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

[TuncayKaraca]

Hi Sreekanth,

I think you need to use ADD_MONTHS_LAST and the code should be like below:

SELECT ADD_MONTHS_LAST(LAST_DAY(CURRENT_DAY), -1) FROM DUMMY;

[ssurampally]

ADD_MONTHS() function also does the same thing., is n’t it? I

[TuncayKaraca]

No, it doesn't! ADD_MONHTS gives you exact date of the reference input date.

For example 2019-03-08 becomes 2019-04-08 with 1 or 2019-02-08 with -1.

Your need is getting the last day of the month, so you need to use ADD_MONTHS_LAST

Have you tried it below?

SELECT ADD_MONTHS_LAST(LAST_DAY(CURRENT_DAY),-1)FROM DUMMY;

[TuncayKaraca]

Hey - Finally I've got a chance to test in HANA 1.0 SPS12 system, and here is the result. I used ADD_MONTHS because ADD_MONTHS_LAST doesn't exist in HANA 1.0 SPS12.

SELECT ADD_MONTHS(LAST_DAY(CURRENT_DATE),-1) "LAST_DAY_OF_PREVIOIS_MONTH" FROM DUMMY;