Timestamp to date in calculated column

[former_member237224]

Hi all,

We have following in a date field 20.141.114.070.000  and want to convert it to something like 22.04.2016 in a calculated column.

Can someone help me here?

Thanks

[pfefferf · Accepted solution]

In a calculated column of a graphical calculation view you can use the "format" function (check docu Date Functions). Assuming your date/time information is stored in longdate format (or converted to it on the fly) an example would look like following.

Calculated column (column VALUE contains the long date):

Result:

In case of a not supported format, you can always use the "component" function to extract the specific parts of the date/time column to build up your requested format.

Regards,

Florian

[former_member237224 · Accepted solution]

Hi,

Thanks for the help but it doesn't work.

See my screenshots

and the error in analysis

what I also need is a right time zone.

This SQL satetement works but not in a calculated column

UTCTOLOCAL (TO_TIMESTAMP("ASSGN_START"), 'cet') as "FOR_ASSGN_START",

[pfefferf · Accepted solution]

What type does your ASSIGN_START column have?

The UTCTOLOCAL function will/can work if you switch in the expression editor from language "Column Engine" to "SQL". Why "can work"? That depends on your HANA release. Which SQL functions are supported or not is not very well documented. I tested the UTCTOLOCAL function on an SPS11 system, there it works. If your system does not support the SQL function in SQL mode, than you could create a user defined table function, do the necessary things in it and use the function as data source in the graphical calc. view.

Regards,

Florian

[varma_narayana · Accepted solution]

Hi Mirza,

Can you just change the data type of the calculated column to DATE (from NVARCHAR) and verify the result once? I believe that should help in your scenario.

regards,

Varma

[former_member237224 · Accepted solution]

We are on SPS9

I need to play around with the table funktions. And there is no way to achive it without the table funktions?

[former_member237224 · Accepted solution]

I have changed it but still the same error

[pfefferf · Accepted solution]

Just checked the old SPS09 documentation. There is already a column engine date function UTCTOLOCAL which does the job.

[former_member237224 · Accepted solution]

I have tried it but without success

[pfefferf · Accepted solution]

Then again the not answered question. What type does your column ASSIGN_START have? It seems not to be a supported type, so you have to convert it to be able to use it with the function. Read the message -> understand it -> act.

[former_member208449 · Accepted solution]

Hello

If the ASSGN_START is changed to DATE/TIME/TIMESTAMP type, you would definitely not get the same error.

[former_member237224 · Accepted solution]

oh sorry it is decimal.

[former_member237224 · Accepted solution]

ok I have it.

utctolocal(longdate("ASSGN_START"), 'CET')

It is very strange that there is a big difference in functions if you use " or '

But thank you all very much. I understand it now much better

[former_member237224 · Accepted solution]

This way it works

utctolocal(longdate("ASSGN_START"), 'CET')

Thank you

[Former Member]

This message was moderated.

[former_member200930]

Are you sure 20.141.114.070.000=22.04.2016? I mean to say, if we convert said values then our output should be 22.04.2016. Need clarity.

[former_member237224]

No it was just an example.

It is more about how to create a output like 22.04.2016 from a timestamp that looks like 20.141.114.070.000 with a calculated column.