Skip to Content
author's profile photo Former Member
Former Member

Convert NVARCHAR to MM/DD/YYYY format

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

4 Answers

  • Best Answer
    Posted on Sep 15, 2015 at 12:46 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Sep 14, 2015 at 07:28 AM

    Hi,

    Can you try the below in your Calculated columns :

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


    Regards,

    Charles

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 14, 2015 at 10:43 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

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

      :


  • author's profile photo Former Member
    Former Member
    Posted on Aug 24, 2016 at 06:11 AM

    This message was moderated.

    Add a comment
    10|10000 characters needed characters exceeded

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.