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:
SELECT
current_date,
YEAR(current_date) "YEAR part",
MONTH(current_date) "MONTH part",
TO_DATE(
YEAR(current_date) || '/' || MONTH(current_date) || '/' || '1', 'YYYY/MM/DD'
) as "First Day"
FROM DUMMY
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.
select
current_date as "today",
add_months(add_days(last_day(current_date), 1), -1) as "first day of month"
from dummy;
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
CREATE FUNCTION FIRST_DAY (iDate date)
RETURNS first_day date
LANGUAGE SQLSCRIPT AS
BEGIN
-- by kodyaz.com
select
add_months(add_days(last_day(:iDate), 1), -1) into "FIRST_DAY"
from dummy;
END;
And HANA database developer can use SQLScript date function First_Day as follows:
select
FIRST_DAY(current_date),
FIRST_DAY('20180423'),
FIRST_DAY('26.04.2018')
from dummy
Let's check the output on SAP HANA database by executing above function calls
