SAP HANA Decimal to timestamp or seconddate SLT

Question

I am using SLT to load tables into our Hana DB. SLT uses the ABAP dictionary and sends timestamps as decimal (15,0) to the HANA DB. Once in the HANA DB via a calculated column in a calculation view, I am trying to convert the decimals to timestamps or seconddates. Table looks like this:

I run a small SLT transformation to populate columns 27-30. The ABAP layer in SLT populates the columns based on the Database transactions.

The problem comes when I try and convert columns 28-30 to timestamps or seconddates. using syntax like this:

Select to_timestamp(DELETE_TIME)
 FROM SLT_REP.AUSP

Select to_seconddate(DELETE_TIME)
 FROM SLT_REP.AUSP

I get the following errors:

Problem being, It works some times as well:

The syntax in calculated column looks like this:

With the error from calculation view being:

Has anyone found a good way to convert ABAP timestamps (Decimal (15,0)) to Timestamp or Seconddate in HANA?

score 1 · Answer 1 · answered Feb 02 '17 at 09:14

1

There are conversion functions available, that you can use here (unfortunately not very well documented).

 select tstmp_to_seconddate(TO_DECIMAL(20110518082403, 15, 0)) from dummy;

 TSTMP_TO_SECONDDATE(TO_DECIMAL(20110518082403,15,0))
 2011-05-18 08:24:03.0

answered Feb 02 '17 at 09:14

Lars Br.

9,949
2
15
29

What is your source? I'm looking to convert varchar '2011-01-01-00.00.00.000000' to timestamp / seconddate – Thorsten Niehues Jun 01 '17 at 13:23
@ThorstenNiehues Source for what? – Lars Br. Jun 01 '17 at 13:33
For this function. I do not find it via Google / Startpage and I would like to browse and see what other functions are available – Thorsten Niehues Jun 07 '17 at 13:50
1

In my presentation https://www.slideshare.net/mobile/LarsBreddemann/innovation-with-sap-hana-using-customisation-what-are-my-options you'll find more functions and how to find them . – Lars Br. Jun 07 '17 at 13:53

score 1 · Accepted Answer · answered Apr 25 '18 at 16:09

The problem was with the ABAP data type. I was declaring the target variable as DEC(15,0). The ABAP extracting the data was rounding up the timestamp in some instances to the 60th second. Once in Target Hana, the to_timestamp(target_field) would come back invalid when a time looked like "20150101121060" with the last two digits being the 60th second. This is invalid and would fail. The base Hana layer did not care as it was merely putting a length 14 into into a field. I changed the source variable to be DEC(21,0). This eliminated the ABAP rounding and fixed my problem.

SAP HANA Decimal to timestamp or seconddate SLT

2 Answers2