Skip to Content

Convert time format from HH:MM:SS:FFn to HH:MM - HANA

Hello All,

I'm new to HANA modelling and I want to convert a time stamp for a column from the format 'HH:MM:SS:FFn' to HH:MM (only hours and minute).

I tried to create a calculated column on top of the actual timestamp and tried various functions. But none works.

Below is actual time stamp column and I'm trying to convert it into HH:MM. Is this possible?

Thanks,

Nivetha

capture.jpg (28.3 kB)
Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • Posted on Jul 10, 2020 at 12:04 PM

    Consider something like this in your view:

    select TO_NVARCHAR( now(), 'HH:MI AM'), TO_NVARCHAR( now(), 'HH24:MI')
    from dummy;
    

    You should be able to put that into a view or your SELECT statement

    Mark

    Add a comment
    10|10000 characters needed characters exceeded

    • Hello Mark,

      Thank you so much for your response. with datatype VARCHAR, I can see the output with this formula. But i want the datatype as TIMESTAMP. Actually I'm trying to create a story on top of this HANA view from SAC and SAC is not taking the dimension with VARCHAR datatype as timestamp.

      I tried with the below and it shows an error while activating in HANA.

      "attribute value is not a date or wrong syntax; $message$=attribute value is not a date or wrong syntax;[here]longdate(string("STATEMENT_START_TIME"), 'YYYY-MM-DD HH:MI AM'), STATEMENT_START_TIME = 2020-07-10 04:16:11.4086100[longdate](6931)"

      Thanks,

      Nivetha

      capture.jpg (79.6 kB)
  • Posted on Jul 10, 2020 at 05:20 PM

    You get the error because TO_TIMESTAMP expects a string as input, not a column that is TIMESTAMP.

    select
    
    TO_TIME( TO_NVARCHAR( now(), 'HH:MI AM') )
    
    , TO_TIME( TO_NVARCHAR( now(), 'HH24:MI') ) 
    
    from TIME_TEST;

    That will convert it back to a TIME. The issue is that you are trying to put it into a TIMESTAMP, but you are ignoring year-month-day. Are you simply trying to display the hour and minute? Or do you want to write this back into a field that is a TIMESTAMP?

    Mark

    Add a comment
    10|10000 characters needed characters exceeded

    • Mark, I want to display the date & time in a format '2020-07-09 05:50 PM'.

      the date can be in any format. But the time i need as HH:MM only and this has to be displayed along with the date.

      And as I said, SAP Analytics cloud doesn't consider this column as time dimension as it is with NVARCHAR datatype.

      how can I create a calculated column on top of an existing object which has the date & time format as 'Jul 10,2020 12.29.10.52119' and display the output simply as 'Jul 10,2020 12.29'? This existing object is created with the datatype 'TIMESTAMP' and I want the same in the new calculated column that i create.

      Thanks,

      Nivetha

  • Posted on Jul 13, 2020 at 05:25 PM

    OK, so I think there are some things to make clearer on this. What is in the database is different from how it is presented to the client.

    With a TIMESTAMP field, it will always have a default format usually based on the client tool (SAC) settings. The only way to guarantee the format would be to convert it to a VARCHAR/NVARCHAR field. Event if you were to create a calc view, the underlying type would still be TIMESTAMP and would thus still have a default display format.

    Within the SAC model you can change the formats of the fields. I would look to SAC to give you the right display formatting over trying to make it work within HANA.

    Mark

    Add a comment
    10|10000 characters needed characters exceeded

    • Mark,

      SAC story is based on a live connection. So I couldn't change the dimension at SAC level. It just pulls the data in a format as in HANA.

      when I create a calculated column on top of a dimension (with a timestamp datatype & in format HH:MM:SS:FFS) with datatype VARCHAR/NVARCHAR, I can see the change in a time format from HANA.

      But SAC is not considering my calculated column (with datatype VARCHAR) as time stamp.

      Will I be able to create a calculated column in the time format YYYY-MM-DD HH:MM with datatype timestamp?

      Thanks,

      Nivetha

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.