7 Replies Latest reply: Mar 22, 2012 4:10 PM by 914688 RSS

    Convert string to a date

    732230
      Gurus,

      It would be highly appreciable if you could help me in knowing how to convert a string to date in obiee. I know we need to use CAST function. I don't have enough material to figure it out myself.

      Awaiting a reply.

      Thanks.
        • 1. Re: Convert string to a date
          gerardnico
          http://gerardnico.com/wiki/dat/obiee/logical_sql/obiee_cast_as_date

          Success
          Nico
          • 2. Re: Convert string to a date
            732230
            Nico - Thanks for the link, this is very helpful. Please tell me what's wrong in the following code. Am enlcosing the code and the error.

            Code 1 :
            CAST(CAST('31-OCT-'||CAST(YEAR("Dim - MBS Loan"."CLOSING DATE") AS CHAR) AS CHAR) AS DATE)

            Error 1:

            State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 17001] Oracle Error code: 25137, message: ORA-25137: Data value out of range at OCI call OCIStmtExecute: select distinct D1.c1 as c1, D1.c2 as c2, D1.c3 as c3,

            Code 2 :
            CAST('31-OCT-'||SUBSTRING(CAST(YEAR("Dim - MBS Loan"."CLOSING DATE") AS CHAR),3,2) AS DATE)

            Error 2:

            State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 17001] Oracle Error code: 1840, message: ORA-01840: input value not long enough for date format at OCI call OCIStmtExecute: select distinct D1.c1 as c1, D1.c2 as
            • 3. Re: Convert string to a date
              gerardnico
              Two questions :
              What is the value of :
              - NLS_DATE_FORMAT in the oracle database ? (do you change it in the connection pool ?)
              - CAST(YEAR("Dim - MBS Loan"."CLOSING DATE") AS CHAR)
              • 4. Re: Convert string to a date
                732230
                Nico,

                following are the values

                - NLS_DATE_FORMAT in the oracle database ? -----> YYYY/MM/DD HH:MI:SS AM/PM
                - CAST(YEAR("Dim - MBS Loan"."CLOSING DATE") AS CHAR) ---------> 1998
                • 5. Re: Convert string to a date
                  gerardnico
                  The function cast as date in oracle depend of your NLS_DATE_FORMAT parameter. If you have the value : YYYY/MM/DD HH:MI:SS AM/PM, you must give the complete syntax :
                  cast('2009/01/01 00:00:00 AM'as date)
                  My advice :
                  Change the NLS_DATE_FORMAT as indicated here :
                  http://gerardnico.com/wiki/dat/obiee/logical_sql/obiee_cast_as_date#feature_enabled
                  You add simply an alter session
                  ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY/MM/DD'
                  Once, you have done it, this formula must work :
                  CAST(CAST(YEAR("Dim - MBS Loan"."CLOSING DATE") AS CHAR) || '/10/31' as date)
                  Success
                  Nico

                  Edited by: gerardnico on Dec 11, 2009 2:34 AM
                  Correction of a parenthesis in the cast function
                  • 6. Re: Convert string to a date
                    AmarendraDivi
                    Hi,

                    in word we can change it text into date, select the format (add ins bi publisher)

                    double click on whatever the field you placed ->it will open bi publisher properties under properties tab formating is there in that you need to place the type as date and format is what ever the format

                    Edited by: Amarendra Divi on Jun 22, 2011 6:30 PM
                    • 7. Re: Convert string to a date
                      914688
                      Hi Nico,

                      Need your valuable suggestions, please help me out with this,
                      I have to calculate total score for monthly level in particular year in OBIEE Answers, I don't have access to RPD.
                      so whatever i have to do, i need to do it from BI Answers.

                      I have a date format mm/dd/yyyy, i need to calculate the total score for mm/yyyy.
                      Please share your ideas, Hope you can easily solve this.

                      Edited by: 911685 on Mar 22, 2012 2:06 PM

                      Edited by: 911685 on Mar 22, 2012 2:07 PM