Skip to Content
avatar image
Former Member

TIMESTAMP format issue in SAP HANA

Hello,

We have a table in Hana where data is coming from ECC via SLT system and there is a column which is storing the TIMESTMP as YYYYMMDDHHMMSS format So, I want to subtract 5 minutes from above TIMESTAMP to achieve this am using add_seconds function by converting it first to TIMESTAMP format later again converting it to string format as YYYYMMDDHHMMSS. I am getting the desired result but I want to keep my TIMESTAMP in 24hrs Format like If I have a TIMESTAMP 20170209143012 in this format then it should need be the same but while doing conversion it is coming as 20170209022512 here TO_TIMESTAMP function converting it to PM and AM format In this case 14 converted to 2 as PM

So, I don't want to change my format it should be in 24hrs format only. I have run the simple SQL on DUMMY to illustrate this example your kind help will highly appreciated.

SELECT to_nvarchar(ADD_seconds ( TO_TIMESTAMP (to_nvarchar( '20170209143012', 'YYYY-MM-DD HH24:MI:SS'),  'YYYY-MM-DD HH24:MI:SS'), -60*5),'YYYYMMDDHHMISS') "TIMESTAMP" FROM   dummy
SQL
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    Feb 09, 2017 at 02:08 PM

    What is stopping you to use the "HH24" format as target format in the final TO_NVARCHAR call?

    SELECT to_nvarchar(ADD_seconds ( TO_TIMESTAMP (to_nvarchar( '20170209143012', 'YYYY-MM-DD HH24:MI:SS'),  'YYYY-MM-DD HH24:MI:SS'), -60*5),'YYYYMMDDHH24MISS') "TIMESTAMP" FROM dummy;

    Regards,
    Florian

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hello Florian,

      Thanks for prompt response.

      It is working perfectly fine as per my requirement. I was not aware that we can use HH24 in final call. It's a nice learning experience for me.

      Thanks alot.

      Regards,

      Ruhul