Oracle Analytics

Get Involved. Join the Conversation.

Topic

    Chethana Kumari
    Unable to convert a varchar field in database to date data...
    Topic posted September 6, 2019 by Chethana KumariBlue Ribbon: 750+ Points, last edited September 11, 2019 
    24 Views, 6 Comments
    Title:
    Unable to convert a varchar field in database to date data type in OAC
    Summary:
    I am creating a dataset from a table in database that has a date field in varchar2 datatype. I am unable to convert it to date datatype in OAC
    Content:

    I am creating a dataset in OAC connecting to a table in database that has a date field in varchar2 datatype. I am unable to convert it to "date" datatype in OAC. I get a query error.

     

    I am trying to use the "Cast(expression as type)" function in data flow to do this conversion. My expression looks like 'Cast(RECORD_DATE AS DATE)'.

    When i Validate this expression and Apply, it succeeds. However there is a query error that shows up in the "preview data" section (attached image). 

    Any help is highly appreciated. Also, please let me know if we could write any custom function or logic in OAC to convert from varchar2 to date datatype.

     

    Note - when I try the same using CSV as my dataset I am able to convert from "varchar2" datatype to "date" datatype. It is giving me this problem when I use database table as my dataset.

    Version:
    OAC version 105.3.0-117
    Image:

    Comment

     

    • Rithwik

      Use                  ---to_date---

    • Aman Jain

      Hello Chethana,

      Can you please try the below formula:

      EVALUATE('TO_DATE(%1,%2)' AS DATE,"Record_Date",'MM-DD-YYYY')

      this should give you the expected output.

      Regards,

      Aman Jain

       

      • Chethana Kumari

        Thanks a tonne for the response Aman.

        I tried this in my data flow and it works fine.

        • Chethana Kumari

          My requirement is to process billions of records whose month and year values are to be extracted to show different visualisations in OAC.

          Thus my concern here is, would there be a hit on performance when I use Evaluate function, to create these charts from this huge no. of records?

          • Aman Jain

            Hello Chethana,

            If you are processing these many records then you must be having an ETL tool, if I am not wrong.

            Rather than using this calculation at the reporting layer, you can push this calculation at the ETL layer and do the reporting on top of your warehouse.

            Please let me know your thoughts on the same.

            Regards,

            Aman Jain

             

            • Chethana Kumari

              Yes Aman, agreed with you. Ideally this needs to be done at the data-pipeline and due to some limitations this was asked to be done in the OAC layer. We are simultaneously looking at doing this at the data-pipeline and that should be the right solution for this kind of dataset.

              Regards,

              Chethana