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

To get the first date of month from a given date, you can run this command. From the example, you can replace the ‘2013-02-28’ date by your own date, or you can use variable when you want to use it inside a procedure.

Code below is code that get the first date of month from a given date. It works by subtracting the given date by the day value of the given date—obtained by using function EXTRACT(DAY FROM [given date]). This will return last month’s last date. So, to get the first date of then month. You need to add 1 to the the day value of the given date.

--First date of month from a given date

SELECT ADD_DAYS('2013-02-28',-EXTRACT(DAY FROM '2013-02-28') + 1) FROM DUMMY; 

The next code is to get the last date of the month. Once you got the first date, you can add 1 month so the date will be the first date of next month and then subtract it by 1 to get the date one day before next month first date which is equal to the last date of given date’s month.

--Last date of month from a given date

SELECT ADD_DAYS(ADD_MONTHS(ADD_DAYS('2013-02-28',-EXTRACT(DAY FROM '2013-02-28') + 1),1),-1) FROM DUMMY; 

 


2 Comments on “SAP HANA: Get the First Date and the Last Date of Month from a Given Date”

  1. kamlesh says:

    thanks for this , but how to calculate first day and last day of current quarter in HANA SQL

  2. First Day of Month for given date:
    SELECT ADD_MONTHS(NEXT_DAY(LAST_DAY(CURRENT_DATE)),-1) FROM DUMMY;

    Last Day of Month for given date:
    SELECT LAST_DAY(CURRENT_DATE) FROM DUMMY;


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s