COEP timestamp conversion in HANA

[Former Member]

Hi,

I am replicating COEP table in HANA. I need to create an additional column for the table which gives the posting date. COEP table has TIMESTAMP column, however the data is stored in DEC 16 format.

I was able to find an ABAP function module / logic to covert it into SQL date.

Please let me know if this can be done in native HANA.

Thanks,

Aamod.

[hemanth_kumar21]

Hi Aamod,

to get the 'DD.MM.YYYY' format below is the script.

SELECT TO_CHAR(TO_DATE(:COEP_FIELD), 'DD.MM.YYYY') INTO IS_VALID FROM dummy ;

change the format according to your requirement.

[lbreddemann]

This is not the correct answer - the data format in the TIMESTMP column is not "DD.MM.YYYY".

[Former Member]

Hi Lars,

Thanks for mentioning it before I could reply to the post, that the timestamp in COEP is little more complex than DD.MM.YYYY.

Looking at the solution in the post I tried to do the same in SQL.

select top 1000 GJAHR,PERIO,ADD_DAYS (TO_DATE ('1990-01-01', 'YYYY-MM-DD'),cast((cast(timestmp/10000 as int)+86400)/86400 as int ) ) "date" from "SAPECC"."COEP"

I also added a generated column to COEP table as:

alter table "SAPECC"."COEP" add ("_DATE" DATE GENERATED ALWAYS as ADD_DAYS (TO_DATE ('1990-01-01', 'YYYY-MM-DD'),((TIMESTMP/10000)+86400)/86400 ))

However the SQL does not take care of converting from UTC to local timezone as is done in the ABAP function module.

I would appreciate if you can give some inputs.

Thanks,

Aamod.

[Former Member]

Hi Lars,

Please let me know if there is a way to convert the COEP timestamp in HANA table generated column.

Thanks,

Aamod.