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 SAP ABAP Programming and HANA Database Tutorials
Development resources, articles, tutorials, code samples and tools and downloads for ASP.Net, SQL Server, R Script, Windows, Windows Phone, AWS, SAP HANA and ABAP, like SAP UI5, Screen Personas, etc.




Install SAP Free


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

SQLScript dates table on SAP HANA database

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

Series Data SQLScript function to create dates table on SAP HANA database


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

SAP HANA database SQLScript functions to create dates table






SAP Tutorials

SAP Tutorial

SAP Forums

SAP Tools

SAP Transaction Codes Table








Copyright © 2004 - 2019 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems