Home > Software engineering >  SAP HANA SQL FLOAT TO DATE
SAP HANA SQL FLOAT TO DATE

Time:11-29

In SAP I have some dates that are stored in tables as a FLOAT, such as shown below for column ATFLV of table AUSP, which has the value 2.0181218000000000E 07:

MANDT OBJEK                     ATINN ATZHL MAFID KLART ADZHL ATWRT ATFLV

100   000000000000004148   9999999400   001 O     023    0000       2.0181218000000000E 07

Other example: enter image description here

I know these are supposed to be dates, but I can't find a function to transform. I have considered using Add_seconds (assuming the values stored are miliseconds), but I don't know what base date to use 1/1/1900 or 1/1/1970?

I appreciate the help.

29.11.2021 EDIT

Just to add some context, I am using HANA DB studio to create a calculation view that does this transformation, using a Table function in HANA SQL.

CodePudding user response:

Thanks to the comments and help from @SandraRossi I have been able to solve my problem using the following:

TO_DATE(LEFT(REPLACE("VALUE_OLD", '.',''),8))
  • Related