3

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:

  • 8 : it's 00:08:00 and not 00:00:08
  • 223 : it's 22:30:00 and not 00:02:23
  • 16321 : it's 01:63:21

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

thanks

Sandra Rossi
  • 11,934
  • 5
  • 22
  • 48
Sano
  • 469
  • 2
  • 6
  • 21

2 Answers2

3

"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.

Antonio Rodríguez
  • 976
  • 2
  • 11
  • 25
0

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)) )
Konrad Z.
  • 1,592
  • 4
  • 20
  • 34
  • hi @Konrad it stored as Integer in format HHMM so it means that 1 = 00:01 as described in my question, TO_TIME wil convert it to 01:00 – Sano Dec 17 '18 at 16:14
  • But in your example you give 16321 -> it's 01:63:21 . So this is not HHMM format. And if 223 => 22:30, then how it's stored 02:23 ? – Konrad Z. Dec 18 '18 at 17:48
  • I think that the proble is this the column UpdateTime use forma HHMMSS but number 10 measn 10 seconds, but column UpdateTS the value 10 means 10 minuts – Sano Dec 19 '18 at 11:03