In this article you will learn some helpful HANA SQLScript date functions that you can use in your transformation routines. These functions make your work much easier because they contain predefined logic that you do not have to program manually. Here I will use examples to explain how you can solve the frequently occurring requirements.
The data type DATE consists of year, month and day information to represent a date value. The standard format of this data type is YYYY-MM-DD. YYYY stands for the year, MM for the month and DD for the day. The range is between 0001-01-01 and 9999-12-31.
With the SQLScript function TO_DATE you can convert a string representing a date into the data type DATE.
The syntax of the function is:
TO_DATE ([String], [Format])For example:
SELECT TO_DATE('2020-09-01', 'YYYY-MM-DD') "DATE Format" FROM DUMMY;Please note that you need the ABAP data type DATS in the format YYYYMMDD for transformation routines. Therefore, if you have an ADSO as the target of the transformation, you must use the SAP HANA function TO_DATS instead.
In contrast to the TO_DATE function, which converts a character string into the DATE format YYYY-MM-DD, the TO_DATS function converts a string into the DATS format YYYYMMDD. This format is required to write data into objects such as ADSOs in BW.
The syntax is very simple:
TO_DATS ([String])
For example:
SELECT TO_DATS('2020-09-01') "DATS Format" FROM DUMMY;
You can combine this function with other SAP HANA functions, such as ADD_DAYS:
OUTTAB = SELECT
TO_DATS(ADD_DAYS(DATE0, 25)) AS DATE0,
CURRENCY, RECORDMODE, AMOUNT, RECORD, SQL__PROCEDURE__SOURCE__RECORD
FROM :INTAB;
A very common requirement is to add days to a date. The ADD_DAYS function makes this task easier for you. This HANA function adds the specified number of days to the given date. Months and years are also automatically incremented.
The syntax of the function is:
ADD_DAYS ([Date], [Number of days])
Where Date indicates the date to be incremented and Number of Days indicates the number of days by which the date is incremented.
In the following example we add 25 days to the respective date as part of a transformation routine:
OUTTAB = SELECT
TO_DATS(ADD_DAYS(DATE0, 25)) AS DATE0,
CURRENCY, RECORDMODE, AMOUNT, RECORD, SQL__PROCEDURE__SOURCE__RECORD
FROM :INTAB;
In addition to days, you can also add months. For this purpose use the SAP HANA function ADD_MONTHS. This adds the specified number of months to the given date.
The function syntax is:
ADD_MONTHS ([Date], [Number of months])
Where Date indicates the date to be incremented and Number of Months indicates the number of months by which the date is increased.
In the following example we add a month to the date:
OUTTAB = SELECT
TO_DATS(ADD_MONTHS(DATE0, 1)) AS DATE0,
CURRENCY, RECORDMODE, AMOUNT, RECORD, SQL__PROCEDURE__SOURCE__RECORD
FROM :INTAB;
This also works for years, as the next example shows:
OUTTAB = SELECT
TO_DATS(ADD_MONTHS(DATE0, 12)) AS DATE0,
CURRENCY, RECORDMODE, AMOUNT, RECORD, SQL__PROCEDURE__SOURCE__RECORD
FROM :INTAB;
The SAP HANA function ADD_YEARS works in the same way. It adds years to the specified date.
Sometimes you need the current date as part of a transformation routine. For example, to determine a key date for the currency translation.
To get the current date, you can use the CURRENT_DATE function. It returns the current local system date.
Here is an example:
OUTTAB = SELECT
TO_DATS(CURRENT_DATE) AS DATE0,
CURRENCY, RECORDMODE, AMOUNT, RECORD, SQL__PROCEDURE__SOURCE__RECORD
FROM :INTAB;
If you work in an international environment, you should use the UTC date instead of the local date. Using local time and converting between local time zones would require additional handling in the code.
Use the SAP HANA function CURRENT_UTCDATE for this:
OUTTAB = SELECT
TO_DATS(CURRENT_UTCDATE) AS DATE0,
CURRENCY, RECORDMODE, AMOUNT, RECORD, SQL__PROCEDURE__SOURCE__RECORD
FROM :INTAB;
Another common requirement is the extraction of months and years from a date. With the EXTRACT function you can easily implement this requirement. You do not have to laboriously find out the individual pieces via SUBSTRING. The function finds out the value automatically.
The syntax of the function is:
EXTRACT ({YEAR | MONTH | DAY | HOUR | MINUTE | SECOND} FROM [Date])
For example, you can read the calendar year 0CALYEAR and the calendar month 0CALMONTH2 from the date 0DATE using the parameters YEAR and MONTH:
OUTTAB = SELECT
DATE0,
EXTRACT(MONTH FROM DATE0) AS CALMONTH2,
EXTRACT(YEAR FROM DATE0) AS CALYEAR,
CURRENCY, RECORDMODE, AMOUNT, RECORD, SQL__PROCEDURE__SOURCE__RECORD
FROM :INTAB;
For the same purpose you can use the SAP HANA functions MONTH and YEAR. The MONTH function returns the number of the month from the specified date. The MONTH function returns the year of a given date:
OUTTAB = SELECT
DATE0,
MONTH (DATE0) AS CALMONTH2,
YEAR (DATE0) AS CALYEAR,
CURRENCY, RECORDMODE, AMOUNT, RECORD, SQL__PROCEDURE__SOURCE__RECORD
FROM :INTAB;
Frequently also the number of days and/or working-days must be computed. For example, in order to determine the delivery reliability of a supplier. For this purpose you can use the functions DAYS_BETWEEN and WORKDAYS_BETWEEN. The DAYS_BETWEEN function returns the number of days between two date values.
The syntax is:
DAYS_BETWEEN ([Start date], [End date])
For example:
OUTTAB = SELECT
DATE0, CALDAY, CURRENCY, RECORDMODE, AMOUNT,
DAYS_BETWEEN(DATE0, CALDAY) AS "/BIC/ZDRDBTWN",
RECORD, SQL__PROCEDURE__SOURCE__RECORD
FROM :INTAB;
However, it makes more sense to use working days for the calculation of delivery reliability. In this case, the WORKDAYS_BETWEEN function can be used. This function allows the factory calendar to be used for the calculation. In BEx Queries, this was previously only possible to a limited extent.
The syntax of this function is:
WORKDAYS_BETWEEN ([Factory calendar], [Start date ], [End date], [Source schema])
The Factory Calendar parameter specifies the ID of a factory calendar in the factory calendar table TFACS. The factory calendar table TFACS must be available in the SAP HANA database to use the function. In SAP BW, SAP CRM and SAP ERP systems running on a SAP HANA database, the TFACS table is located in the ABAP schema SAP<SID>. For other SAP HANA databases the table TFACS can be replicated from a SAP Business Suite system.
The result is returned as an INTEGER number.
If the [Start Date] is less than or equal to the [End Date], then the number of working days in the period starting with the [Start Date] and ending with the [End Date] is returned. The number of days returned includes the day of the [Start Date] but excludes the day of the [End Date].
If [Start Date] is later than [End Date], a negative number of working days will be returned in the period starting with [End Date] and ending with [Start Date]. The number of days returned includes the day of [End Date] but excludes the day of [Start Date].
Below is an example:
OUTTAB = SELECT
DATE0, CALDAY, CURRENCY, RECORDMODE, AMOUNT,
WORKDAYS_BETWEEN('10', DATE0, CALDAY) AS "/BIC/ZDRDBTWN",
RECORD, SQL__PROCEDURE__SOURCE__RECORD
FROM :INTAB;
Sometimes it is necessary to time stamp records. You can use the TIMESTAMP function for this purpose. This function returns the current timestamp of the local system.
However, the HANA internal format for the timestamp is YYYY-MM-DD HH24:MI:SS.FF7. FF.
The format of the BW time stamp 0TCTTIMSTMP, on the other hand, is YYYYMMDDHHMMSS.
Therefore, you have to convert the type with the TO_NVARCHAR function:
TO_NVARCHAR(CURRENT_TIMESTAMP, 'YYYYMMDDHHMMSS')
Here is an example of the transformation routine:
PROCEDURE FOR HDB LANGUAGE SQLSCRIPT OPTIONS READ-ONLY.
OUTTAB = SELECT
DATE0,
TO_NVARCHAR(CURRENT_TIMESTAMP, 'YYYYMMDDHHMMSS') AS TCTTIMSTMP,
CURRENCY, RECORDMODE, AMOUNT, RECORD, SQL__PROCEDURE__SOURCE__RECORD
FROM :INTAB;
If you work in an international environment, you should use UTC time instead of local timestamps. Using local time and converting between local time zones may require additional handling in the application code.
In this case, use the CURRENT_UTCTIMESTAMP function. This function selects the current UTC timestamp. For example:
OUTTAB = SELECT
DATE0,
TO_NVARCHAR(CURRENT_UTCTIMESTAMP, 'YYYYMMDDHHMMSS') AS TCTTIMSTMP,
CURRENCY, RECORDMODE, AMOUNT, RECORD, SQL__PROCEDURE__SOURCE__RECORD
FROM :INTAB;
As you can see, the switch from ABAP to SQLScript can be made easier thanks to predefined functions. These cover the most common requirements.
Would you like to learn more about SAP HANA SQLScript applications? Download our current whitepaper on this topic! If you would like to get support in defining the right HANA strategy for you, please do not hesitate to contact us.