Skip to Content
Technical Articles
Author's profile photo Poonam Nikam

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

Welcome ABAPERs to this series on learning 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




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

from dummy;



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



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;

O/p O/p%20-%204.2%20Query


This blog explained predefined SQL scripting functions and how to apply them to generate calculative results in the SAP HANA database. I hope you have learned about these functions and how to use them in SAP HANA. 

I have explained the OIA scenario in more detail along with an example in another blog post. Follow me if you found these skills valuable. Stay tuned for more technical updates and opportunities to gain hands-on experience with SAP HANA.

I look forward to hearing your views and suggestions. Please share any areas for improvement.

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Jelena Perfiljeva
      Jelena Perfiljeva

      Thanks for sharing the examples! There is also a very good CDS cheat sheet that has a longer list of functions.

      Author's profile photo Poonam Nikam
      Poonam Nikam
      Blog Post Author

      Hello Jelena,

      Thanks for sharing this informative doc! Its really helpful!

      I will try to cover additional functions with examples in my upcoming posts.