Create Dates Table using SQLScript on SAP HANA Database
SQLScript developers creating SQL codes on SAP HANA database can create dates table easily using Series_Generate function which enables effective creation of series data on HANA DB. Various forms of SQLScript Series_Generate function, like SERIES_GENERATE_DATE, SERIES_GENERATE_INTEGER exist providing great flexibility for SQL programmer to create series data in targeted data types.
For example, HANA database programmers can find the solution for to create numbers table on SAP HANA database using SQLScript series_generate_integer function I shared for other developers
In this SAP HANA Database SQLScript tutorial, I want to show how SQL programmer can create dates table on HANA database using series_generate_date Series Data function.
Create Dates Table with a Certain Number of Entries
Assume that as a HANA database programmer, you require a SQL dates table in your SQLScript so that it will start from a certain date which is parametric (can be defined using a variable) and will contain a certain number of date entries in it.
Let's make the requirement more solid.
Assume you require a SQL dates table starting from the first date of the current month and will containg 15 days.
Below is the SQLScript code for developers to create dates table with 15 rows starting from first date of the current month.
declare numberofdays int := 15;
declare date_start date := FIRST_DAY(current_date);
SELECT generated_period_start as date
FROM SERIES_GENERATE_DATE('INTERVAL 1 DAY', :date_start, add_days(:date_start, :numberofdays));
The result of the execution of above SQL code block will be as follows
If the sequence number order the order number of the date entry is also required, you can use the element_number column of the SERIES_GENERATE_DATE table function as seen in below code.
declare numberofdays int := 15;
declare date_start date := FIRST_DAY(current_date);
SELECT element_number, generated_period_start as date
FROM SERIES_GENERATE_DATE('INTERVAL 1 DAY', :date_start, add_days(:date_start, :numberofdays));
Create Dates Table with Days in a Given Date Range
If the requirement it to create a dates table covering the days in a given date range, then actually SQLScript programmer already knows the boundaries of the dates period.
But still we can not use directly the last day as an input parameter to the SERIES_GENERATE_DATE SQLScript date function.
Because SERIES_GENERATE_DATE function will ignore the last value or exclude the last value from the generated series data.
It is better to add one more item by extending the period for the series date function using add_days function for one day.
In this case, our SQL code will be as follows.
declare date_start date := '01.01.2018';
declare date_end date := '31.01.2018';
SELECT element_number, generated_period_start as date
FROM SERIES_GENERATE_DATE('INTERVAL 1 DAY', :date_start, add_days(:date_end,1));
