This discussion is archived
7 Replies Latest reply: Mar 22, 2012 2:10 PM by 914688 RSS

Convert string to a date

732230 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    http://gerardnico.com/wiki/dat/obiee/logical_sql/obiee_cast_as_date

    Success
    Nico
  • 2. Re: Convert string to a date
    732230 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points