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
Development resources, articles, tutorials, code samples, tools and downloads for SAP HANA and ABAP, HANA Database, SQLScript, SAP UI5, Screen Personas, Web Dynpro, Workflow

Convert Inverted Date in SAP HANA Database using SQL


In this SAP HANA SQLScript tutorial, SQL developers find tips to convert inverted date to date using SQL functions, from DATUM_INV domain to DATUM domain data type. In SAP, some date information isnot stored in data columns of type DATS, the expected preferred data type for date values. Instead, like the case in ABAP table TCURR Exchange Rates, date is stored in inverted date as for GDATU table field.

ABAP programmers developing SQL codes on SAP HANA Studio on SAP HANA database, who needs to convert inverted date column values of Exchange Rates table TCURR, GDATU field can refer to ABAP conversion exit routine CONVERSION_EXIT_INVDT_OUTPUT.

HILF1 = '99999999' - INPUT.
TRANSLATE HILF1(5) USING ' 0'.
CONDENSE HILF1 NO-GAPS.
OUTDATUM = HILF1.
WRITE OUTDATUM TO OUTPUT DD/MM/YYYY.
Code

As ABAP developers can understand easily, the inverted date is simply calculated by substracting the input date field from static '99999999' value.
This conversion between DATE and Inverted Date parameters is something SQL developers can easily handle on SAP HANA database

For example, to see the corresponding date information on TCURR currency rates table GDATU column, following SQL Select can be executed.

select
 gdatu,
 to_date( to_nvarchar(99999999 - gdatu) ) gdatu_date,
 YEAR( to_date( to_nvarchar(99999999 - gdatu) )) as Year,
 MONTH( to_date( to_nvarchar(99999999 - gdatu) )) as Month,
 DAYOFMONTH( to_date( to_nvarchar(99999999 - gdatu) )) as Day,
 substring(to_date( to_nvarchar(99999999 - gdatu) ), 1,4) as _Year,
 substring(to_date( to_nvarchar(99999999 - gdatu) ), 6,2) as _Month,
 substring(to_date( to_nvarchar(99999999 - gdatu) ), 9,2) as _Day
from "SAPS4S".TCURR
order by gdatu
Code

Here is the output

convert inverted date to ABAP date using HANA SQLScript

If you check the above SQLScript code block the conversion from inverted date to date value is done by "to_date( to_nvarchar(99999999 - gdatu) )"

I also added additional fields in the SELECT list to show how SQL programmers can manipulate date data using HANA SQLScript Date functions and SubString() function to fetch related date parts as an alternative method.

The converted date in its string representation is "YYYY-MM-DD"


SAP HANA SQLScript Function to Convert Inverted Date

Following user-defined function can be created on HANA database to use for inverted date conversion to DATE data type

CREATE FUNCTION ConvertFromInvertedDate (
 invertedDate VARCHAR(8)
)
returns toDate date
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
AS
BEGIN

 toDate := to_date( to_nvarchar(99999999 - :invertedDate) );

END
Code

Here is how ConvertFromInvertedDate conversion function can be used in SQL codes

select
 gdatu,
 ConvertFromInvertedDate(gdatu) gdatu_date,
 YEAR( ConvertFromInvertedDate(gdatu) ) as Year,
 MONTH( ConvertFromInvertedDate(gdatu) ) as Month,
 DAYOFMONTH( ConvertFromInvertedDate(gdatu) ) as Day,
 substring(ConvertFromInvertedDate(gdatu), 1,4) as _Year,
 substring(ConvertFromInvertedDate(gdatu), 5,1) as _Year,
 substring(ConvertFromInvertedDate(gdatu), 6,2) as _Month,
 substring(ConvertFromInvertedDate(gdatu), 9,2) as _Day
 from "SAPABAP1".TCURR
order by gdatu_date desc;
Code

Output of the above SQL Select statement on a very seldomly used SAP system executed on TCURR field produced below data.

SAP HANA database SQLScript function for inverted date conversion



SAP HANA and ABAP

Install SAP Free
CRM Companies List
Web Based CRM Software


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.