How to calculate last day of month in calculated coulmn window

[Former Member]

Hi Experts,

Today is 20th Oct 2015. I want last day of current month i.e. 31. So, could you please let me know, how to do that in calculate column window.

[former_member392982]

This message was moderated.

[michael_eaton3]

This message was moderated.

[Former Member]

Here I share solutions that I found for this kind of issues. I hope it will be useful.

Create these Calculated Columns in Calculation View with following codes.

Name : First_Day_of_Month

Expression

date("0CALMONTH")

Name : Next_First_Day

Expression

if(rightstr("0CALMONTH",2)='12',adddays(date("0CALMONTH"+'01'),31),date(int("0CALMONTH")+1))

Name : Last_Day_of_Month

Expression

"Next_First_Day"-1

This solution also avoids 12th-1st month conflict or error. If you have "000000" Calmonth data you should need also exclude it.

date("0CALMONTH")

this automatically assigns first day of calendar month.

[Former Member]

Hi Umesh,

Check this blog , it may be helpful.

SAP HANA: Get the First Date and the Last Date of Month from a Given Date | WuaWua

and also have a look into this.

LAST_DAY - SAP HANA SQL and System Views Reference - SAP Library

Regards,

Malthesha Sungar

[Former Member]

Hi Sungar,

These are SQL functions and not applicable in SAP HANA calculated column window. it have tried everything but it is not working.

Thanks for your reply.