CODEWITHSUNDEEP
sqlhana
Sano
Sano

Reputation: 539

SAP HANA SQL how to convert Date and Time to timestamp

I am trying to concatenate the CreateDate and CreateTS columns to make one Timestamp that contains the date and time.

Right now the only solution I reached is this:

SELECT 
    TO_TIMESTAMP(
        to_char(COALESCE("UpdateDate","CreateDate",'1970-01-01'), 'yyyy-mm-dd') || ' '||
        SUBSTRING (lpad(COALESCE("UpdateTS","CreateTS",0),6,'0'),0,2) || ':'||
        SUBSTRING (lpad(COALESCE("UpdateTS","CreateTS",0),6,'0'),3,2)|| ':'||
        SUBSTRING (lpad(COALESCE("UpdateTS","CreateTS",0),6,'0'),5,2), 'YYYY-MM-DD HH24:MI:SS'
    )  AS update_create
FROM OCRD

I found out that with UpdateTime column this code isn't working. The big problem is that the date and time are distinct columns, and also it saves the time in different formats:

Is there any already defined function to concatenate the date and time to get the real timestamp of the create/update?

thanks

Upvotes: 3

Views: 9834

Answers (2)

Antonio Rodríguez
Antonio Rodríguez

Reputation: 1146

"UpdateDate" and "CreateDate" are stored as Date in SAP HANA, keeping year, month, day and 00:00:00.000 for time.

"CreateTS" and "UpdateTS" store just the time in format hhmmss as int. That means that 14:34:06 is stored as 143406. As an int, leading zeros are not stored.

The way to join date and time that worked for me is to create a varchar for date (format YYYYMMDD) and a varchar for time adding leading zeros. Then, creating a TimeStamp using date and time.

Solution:

TO_TIMESTAMP(TO_VARCHAR(COALESCE("UpdateDate","CreateDate",'19700101'), 'YYYYMMDD') || ' ' || LPAD(COALESCE("UpdateTS","CreateTS",0),6,0),'YYYYMMDD HH24MISS')

You can create a function to simplify this:

CREATE FUNCTION TO_DATETIME(vDATE DATE, vTime INT)
RETURNS DT TIMESTAMP
AS
BEGIN
    DT := TO_TIMESTAMP(TO_VARCHAR(:vDATE, 'YYYYMMDD') || LPAD(:vTIME,6,'0'), 'YYYYMMDDHH24MISS');
END;

Then just use it: SELECT "UpdateDate", "UpdateTS", TO_DATETIME("UpdateDate", "UpdateTS") as "UpdateDateTime" FROM OCRD

EDIT: Sometimes CreateTS or UpdateTS are stored as HHMM instead of HHMMSS. Be careful with that either padding right with '00' or creating a new function for those cases.

Upvotes: 3

Konrad Z.
Konrad Z.

Reputation: 1652

If your columns "UpdateDate","CreateDate" are stored as dates and columns "UpdateTS","CreateTS" store in format HHMMSS then you can try following:

TO_TIMESTAMP( COALESCE("UpdateDate","CreateDate",'1970-01-01') || ' ' || TO_TIME(COALESCE("UpdateTS","CreateTS",000000)) )

Upvotes: 0

Related Questions