Convert NVARCHAR to MM/DD/YYYY format

[Former Member]

hi all,

I need to convert all the dates (in NVARCHAR) to MM/DD/YYYY format. I need to achieve this in calculated columns.i have tried searching, but not getting clear idea.

the client doesn't want to convert in IDT or webi. I tried using date(ERDAT) but  its coming as Aug 14,2015. But I need in MM/DD/YYYY format so that no conversion is required in webi or universe.

Is there any function of to achieve this?

thank you

[lucas_oliveira · Accepted solution]

Hello Ash,

What you're facing is the actual Date cell formatting from HANA Studio kicking in. You're using data type DATE on your calculated column and that will tell HANA Studio's cell formatter "Hey! format me as a Date!".

You can deactivate that behaviour and have the raw date format by doing the following: Menu Window -> Preferences -> SAP HANA -> Runtime -> Result and then uncheck 'Format Values'. Needless to say you'll lose formating for other datatypes (integers and decimals for instance).

However, that's only happening in HANA Studio due to its cell formatting. If you run that same query on a different tool, you'll have the raw format (which by default is YYYY-MM-DD). Unless the front-end tool formats it as well just as HANA Studio does. Run it from IDT and see what's in there.

On SP10 you can surely use SQL functions in calculated columns expression editor. In fact, Sp10  allows you to choose between Calculation/SQL Engine functions. That's not available in SP09.

By the way, using TO_DATE("yourdatecolumn", 'MM/DD/YYYY') will not work, as the format here is not supported. Please check the official documentation on that:

TO_DATE - SAP HANA SQL and System Views Reference - SAP Library

Data Types - SAP HANA SQL and System Views Reference - SAP Library

However, what you're looking for is just string formatting. Then, using to_varchar (or to_nvarchar) can help you formatting the way you need. That can surely be done by the front-end (universe) or ,as mentioned, by using SP10 with SQL functions in the calculated column. The option I can see in SP09 is to use string processing functions from the Calculation engine to build up the desired results.

BRs,

Lucas de Oliveira

[former_member196918 · Accepted solution]

This message was moderated.

[Former Member · Accepted solution]

This message was moderated.

[Former Member]

This message was moderated.

[Former Member]

Hi,

Do the TO_DATE("fieldname", 'MM/DD/YYYY') and pass the data to the front end, it would have converted and sent it in proper format. But upon data preview, it might display in different format due to the NLS language settings of the database.

You can also try create a calculated column as TO_CHAR(TO_DATE("fieldname", 'MM/DD/YYYY'), 'MM/DD/YYYY'). Which should display it correctly.

Regards,

Anil

[Former Member]

hi all,

thanks your inputs.

I had created a column of varchar and the syntax i have as NOW().

in another column, i want to display the above column is MM/DD/ YYYY. I created a column of date type,

In the express editor i have tried :

TO_DATE ("fieldname",'MM/DD/YYYY') and also O_CHAR(TO_DATE("fieldname", 'MM/DD/YYYY'. tried  SQL language of the expression editor. I am getting an error

:

[293]: argument type mismatch:Currently only the column store expresson language CS is supported.

I also tried with COLUMN option in language of the expression editor and gave the formula to_varchar (or to_nvarchar). Its giving an error.

:

[former_member1334]

Hi,

Can you try the below in your Calculated columns :

TO_DATE ("fieldname",'MM/DD/YYYY')

Regards,

Charles