﻿ Calculate First Day of Month using SQLScript on SAP HANA Database
 SAP ABAP Programming and HANA Database Tutorials Development resources, articles, tutorials, code samples, tools and downloads for SAP HANA and ABAP, HANA Database, SQLScript, SAP UI5, Screen Personas, Web Dynpro, Workflow

Calculate First Day of Month using SQLScript on SAP HANA Database

SAP HANA database developers working with SQLScript date and time functions can calculate first day of month using different ways including creating user function First_Day() as shown in this tutorial. Unfortunately, there is not a build-in first day function like LAST_DAY function in SQLScript or like EMONTH() End of Month function.

If you are a SQL developer who has hands-on experience on SQL Server, first day of month can be calculated as shown in the referenced tutorial.

Of course it requires a logic to find the last day of the month like 30, or 31 days or February with 29 days on the other hand, etc.

On the contrary, to get the first day of month is quite easy by concatenating date parts to build a date variable.
Calculate YEAR and MONTH then add 1 or 01 for the first day. Then build a date variable from concatenated date string.
Here is the codes for this method for first day of month calculation:

But I'ld rather prefer using the SQLScript LAST_DAY function.
Using additional SQL date functions like ADD_DAYS() and ADD_MONTHS() enable HANA database developers to calculate the first day of month in SQLScript.

Here is how database programmers can use SQLScript date functions ADD_DAYS(), ADD_MONTHS() and LAST_DAY() functions to calculate the first date of current month.

The output of the above SQLScript code execution results as follows

Of course, it is best to encapsulate the above SQL code into a user-defined SQL function will make the first day of month calculation parametric with input date variable and reusable for SQLScript developers on SAP HANA database systems.

Here is the CREATE script of First_Day SQLScript function

And HANA database developer can use SQLScript date function First_Day as follows:

Let's check the output on SAP HANA database by executing above function calls

SAP HANA and ABAP