Most common built-in SQL functions that are provided with SAP HANA.

This blog on SAP HANA SQL script functions aims to make you comfortable with some of the important built-in SQL scripting functions.

SQL script is the scripting language used in the SAP HANA database. We can perform data-intensive operations in the SAP HANA database and transfer only a small result set to the application layer. This way, we can optimize the performance of a program using SQL script because we communicate with the database in its own language.

Let’s understand these functions with a practical scenario. Typically, we are asked to generate an Open Item Analysis (OIA) report. In simple terms, OIA helps businesses understand customers who have pending payments for invoices where delivery of the products has already occurred. We can achieve OIA by implementing SQL script code inside any HANA container such as ADBC, AMDP, scripted calculation view, or directly by writing a program/query in HANA using SQL script. 

Many of us are intimidated by SQL scripting, but let me assure you that you can easily use script functions if you go through this blog and the examples below.

Below, we will learn about predefined SQL functions which are useful when implementing Open Item Analysis scenarios using any of the methods mentioned above. I have used a ‘dummy’ read-only database table provided by SAP for extracting information and the ‘snwd_so_inv_head’ table to fetch invoice header information. We should specify the schema name before the database table, which will help the system identify the schema where the database table is located (applicable only when fetching data from cross-schema). Ex: saphanadb.snwd_so_inv_head.

List of functions along with syntax and examples:

– now(): To get the current date and time

– localtoutc(timestamp, specify time zone for conversion): To convert local time to Coordinated Universal Time (UTC) 

– to_timestamp(date, specify time zone): To convert a date string to TIMESTAMP data type format

– left(date, mention number of chars): To get the specified number of characters starting from the extreme left position

– seconds_between(date1, date2): To get the number of seconds between two specified dates

– floor(number): To get the largest integer value 

– round(number, specify number of decimals to round): To round a number to a specified number of decimal places

– days_between(date1, date2): To calculate the number of days between two dates

Ex.1.

Ex.2.

select now(), LOCALTOUTC(now(),‘CET’), to_timestamp(LOCALTOUTC(now(),‘CET’))

from dummy;

Ex.3.

select left(changed_at, 10) as left_val from saphanadb.snwd_so_inv_head

Ex.4.

select left(changed_at, 14) as left_val, to_timestamp(left(changed_at, 14),‘YYYYMMDDHHMISS’) as time_stmp, Floor((seconds_between(to_timestamp(left(changed_at, 14),‘YYYYMMDDHHMISS’), to_timestamp(localtoutc(now(),‘CET’)))) / (24* 60 * 60)) as days_f, round(((seconds_between(to_timestamp(left(changed_at, 14),‘YYYYMMDDHHMISS’), to_timestamp(localtoutc(now(),‘CET’)))) / (24* 60 * 60)),0) as days_r, days_between(to_timestamp(left(changed_at, 14),‘YYYYMMDDHHMISS’), to_timestamp(localtoutc(now(),‘CET’))) as days_n from saphanadb.snwd_so_inv_head;

This blog explained predefined SQL scripting functions and how to apply them to generate calculative results in the SAP HANA database.