5 Replies Latest reply: Mar 15, 2011 4:22 PM by 823928 RSS

    Casting VARCHAR to DATE in OBIEE Report

    735477
      Hi,

      When I change the datatype from VARCHAR to DATE (using CAST() function) on my report. I am not getting the results.

      Error: Datetime value 08/20/2009 from 08/20/2009 does not match the specified format. (HY000)

      I tried to change the datatype of the field in the metadata physical layer. But it only got three options as Type: DOUBLE, UNKNOWN, VARCHAR...so there is no DATE datatype. I also enabled the CAST_SUPPORTED DB feature in the Subject Area too, Did not worked!

      FYI: We import data from SAP, it all comes in as either VARCHAR or INTEGER.

      Thanks in advance!
        • 1. Re: Casting VARCHAR to DATE in OBIEE Report
          646090
          Hi,

          I'm going to assume that the data type of your column is a string and not an integer. If it is an integer, the below code will work if you cast the integer to a CHAR before you use the SUBSTRING.

          CAST(SUBSTRING( <YOUR_COLUMN> FROM 4 FOR 2) || '-' ||
          CASE WHEN SUBSTRING( <YOUR_COLUMN> FROM 1 FOR 2) = '01' THEN 'Jan' WHEN SUBSTRING( <YOUR_COLUMN> FROM 1 FOR 2) = '02' THEN 'Feb' WHEN SUBSTRING( <YOUR_COLUMN> FROM 1 FOR 2) = '03' THEN 'Mar' WHEN SUBSTRING( <YOUR_COLUMN> FROM 1 FOR 2) = '04' THEN 'Apr' WHEN SUBSTRING( <YOUR_COLUMN> FROM 1 FOR 2) = '05' THEN 'May' WHEN SUBSTRING( <YOUR_COLUMN> FROM 1 FOR 2) = '06' THEN 'Jun' WHEN SUBSTRING( <YOUR_COLUMN> FROM 1 FOR 2) = '07' THEN 'Jul' WHEN SUBSTRING( <YOUR_COLUMN> FROM 1 FOR 2) = '08' THEN 'Aug' WHEN SUBSTRING( <YOUR_COLUMN> FROM 1 FOR 2) = '09' THEN 'Sep' WHEN SUBSTRING( <YOUR_COLUMN> FROM 1 FOR 2) = '10' THEN 'Oct' WHEN SUBSTRING( <YOUR_COLUMN> FROM 1 FOR 2) = '11' THEN 'Nov' ELSE 'Dec' END
          || '-' || SUBSTRING( <YOUR_COLUMN> FROM 7 FOR 4) AS DATE)


          Replace <YOUR_COLUMN> with the actual name of your column.

          You can also go the way of EVALUATE,

          EVALUATE('TO_DATE(%1,%2)' AS DATE,<YOUR_COLUMN>,'mm/dd/yyyy')

          Good luck and if you find this useful, please award points!

          Best regards,

          -Joe
          • 2. Re: Casting VARCHAR to DATE in OBIEE Report
            646090
            Hi,

            One more thing I wanted to give you. Nico has an excellent article about how the CAST function works in OBIEE. See it here.

            Best regards,

            -Joe
            • 3. Re: Casting VARCHAR to DATE in OBIEE Report
              735477
              Hi,
              Thanks for the help!

              I got the following error!

              Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
              State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 46046] Datetime value 08/20/2009 does not match the specified format. (HY000)
              SQL Issued: SELECT "Company code"."Company Name" FROM Project WHERE ("Company code"."Company Code" = '3000') AND (CAST("WBS Element"."Created on" AS DATE) BETWEEN date '2010-01-06' AND date '2010-01-06') ORDER BY 1

              Its the format error. I think we need to convert the mm/dd/yyyy to yyyy-mm-dd format. I guess calendar feature uses yyyy-mm-dd format. So, how can I convert the date format?
              I have in NQSConfig.ini file
              DATE_TIME_DISPLAY_FORMAT = "yyyy/mm/dd hh:mi:ss" ;
              DATE_DISPLAY_FORMAT = "yyyy/mm/dd" ;
              TIME_DISPLAY_FORMAT = "hh:mi:ss" ;
              • 4. Re: Casting VARCHAR to DATE in OBIEE Report
                646090
                Hi KSO614,

                Try setting NLS_FORMAT to the same format that you're seeing the logical SQL. Nico's article shows you how to do it. It's the section that is titled "Feature Enabled - Cast as date with Oracle".

                Also, can you walk me through the DATE casts being performed in that SQL statement. I seem to see three different DATE casts. You might want to start with just one first and get that working, the move on to the rest.


                -Joe
                • 5. Re: Casting VARCHAR to DATE in OBIEE Report
                  823928
                  Hi,

                  I have a scenario where the user wants to display per day for the past 90 days from the present day ,
                  and he should also have the privilege to select the data range between the 90 days

                  For ex: if he selects 20 days , the report should display 20 indiavidual dates, similar if select 30 , should display 30 indiviaul dates.

                  Working on importing SAP BW cubes. the dates are in varchar datatype. Created a logical column for this and converted in to DATE format.

                  CAST ( SUBSTRING( CAST ("ZMHCMES1/ZHCM_ZMHCMES1_ESS_ERC_STAT1"."Calendar day"."Calendar day Level 01" AS VARCHAR ( 20 )) FROM 7 FOR 4) || SUBSTRING( CAST ("ZMHCMES1/ZHCM_ZMHCMES1_ESS_ERC_STAT1"."Calendar day"."Calendar day Level 01" AS VARCHAR ( 20 )) FROM 6 FOR 1) || SUBSTRING( CAST ("ZMHCMES1/ZHCM_ZMHCMES1_ESS_ERC_STAT1"."Calendar day"."Calendar day Level 01" AS VARCHAR ( 20 )) FROM 1 FOR 3) || SUBSTRING( CAST ("ZMHCMES1/ZHCM_ZMHCMES1_ESS_ERC_STAT1"."Calendar day"."Calendar day Level 01" AS VARCHAR ( 20 )) FROM 4 FOR 2) AS DATE )

                  When i pull this column on the Answer able to see the data. but when i try to apply filter on any of this date.

                  It throws me an error.
                  A general error has occurred. [nQSError: 46046] Datetime value 1900/01/01 does not match the specified format. (HY000)

                  Anybody can youplease helpme out with this.