SAP HANA ABAP: SQLScript Date/Time Functions

As we all know that in any reporting or data analysis system, the main objective is to convert the source data into the meaningful information which can be of business relevance. This is to facilitate the stake holders to take decisions effectively and efficiently.

If we break the above scenario to the HANA developer level, we need to have the knowledge on various functions available in SQLScript to develop a meaningful information for reporting and make our life easier as a developer.
There are various types of functions available in SAPScript, in this blog I would be spread some light on the date and time functions readily available to be used in CDS and AMDP, while writing SQLScript code.

Scenario1:

• In an AMDP method you want an additional/calculated column which should have the current date, in Open SQL ( SE38 Traditional ABAP program) we use SY-DATE system field to achieve this. Unfortunately we cannot use the system fields in AMDP method. So below is the function with a sample code, we shall use in the select statement:
• Function Name: CURRENT_DATE
• Sample:

ex_data = SELECT matnr,
                 matkl,
                 CURRENT_DATE as sday
          FROM mara      
          WHERE matnr = im_matnr;

In the above sample, sdate would be the calculated column having the current date.Similarly, we have CURRENT_TIME, CURRENT_TIMESTAMP, CURRENT_USER( this one is not a date/time function though) functions available.

Scenario2:

• In the same method if you want columns/fields which will have current year or current month.
• We shall use the below function:
• Function Name: EXTRACT ({YEAR | MONTH | DAY | HOUR | MINUTE | SECOND} FROM <d>)
• Sample:

ex_data = SELECT matnr,
                 matkl,
                 CURRENT_DATE as sday,
                 EXTRACT ( month from CURRENT_DATE ) as cmon,
                 EXTRACT ( year from CURRENT_DATE ) as cyear,
            FROM mara
            WHERE matnr = im_matnr;

In the above sample, cmon and cyear would be calculated columns having current month and current year. Similarly you can extract day, hour, minute and second.

‘Some of you might be able to relate it with Timed Data which is generated in the _SYS_BI schema of the modelling perspective”.

Senario3:

• If you want to add a particular number of days to a date value.
• Function name: ADD_DAYS (<d>, <n>)
• Sample:

ex_data = SELECT matnr,
                 matkl,
                 add_days (to_Date( CURRENT_DATE ),30) as sdate
            FROM mara
            WHERE matnr = im_matnr;

The above query will give us a calculated column with the date 30 days from the current one.

Similarly if instead of 30 you write ‘-30’ the query would give us a date 30 days previous to today’s date.

In the similar way, you can use the following functions: ADD_MONTHS, ADD_MONTHS_LAST, ADD_YEARS, ADD_SECONDS, ADD_WORKDAYS.

Scenario 4:

• If you want to fetch the day name from a particular date.
• Function name: DAYNAME (<d>)
• Sample:

ex_data = SELECT matnr,
                 matkl,
                 DAYNAME ( CURRENT_DATE ) as sday
            FROM mara
          WHERE matnr = im_matnr;

The above query will give us a calculated column with name of the today’s day for eg: Sunday.

In addition to the above function we have: DAYOFMONTH, DAYOFYEAR,DAYS_BETWEEN, WEEK, QUARTER, NEXT_DAY.

Scenario 5:

In all the above samples I have used the CURRENT_DATE as a value for the functions, if you want to manipulate the date and time values present in the table as a column, you can directly pass the column name which is of date type to the functions. For eg:

ex_data = SELECT matnr,
                 matkl
                 DAYNAME( ersda ) as sday
           from mara;

Here field3 should be any table field having date as entry.

So with the help of above mentioned scenario we have seen examples of various date and time functions which we can utilize while SQLScrip coding in AMDP/CDS/Scripted Calculation view.

Thank you for reading, hope the information will be useful for all the relevant developers. I shall keep on exploring ,using and sharing new functions.