SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
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:

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
Code

first day of month using SQLScript on HANA database

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;
Code

The output of the above SQLScript code execution results as follows

calculate first day of month in SQLScript on SAP HANA database

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;
Code

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
Code

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

SQLScript First_Day SQL function for SAP HANA Database developer



SAP HANA and ABAP

Install SAP Free
CRM Companies List
Web Based CRM Software


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.