Oracle Analytics Cloud and Server

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Convert char to date format in obiee 11g?

Received Response
2851
Views
10
Comments

Hi,

I want to Convert char to date format in obiee 11g.

i have this date

'07/31/'||CAST(YEAR(CURRENT_DATE) AS CHAR)

I want to change  char to date format.

please help me,

Thanks in advance,

A.kavya.

Answers

  • Niamh O
    Niamh O Rank 2 - Community Beginner

    Hi there,

    Is this something you can add to your date dimension table? For example, in my date dimension, ever row has a column called: PYE_DATE_CHAR so that I can easily grab Previous Year End (eg '31 Dec 2015') in the desired format, on every row for a year it has the same value. Make your database do the work here especially the date table . Cleaner and better performance.

    If that isn't an option or suit the requirement, use some substring there:

    eg. '07/31/' || SUBSTRING(CAST(CURRENT_DATE AS CHAR) FROM 1 FOR 4)

    (first CAST current_date as a CHAR to see where the YYYY is in the string).

    Good luck, Niamh

  • Hi,

    as you , i have tried , i am getting..output like yyyy/mm/dd.

    my date is as follows

    '07/31/' || SUBSTRING(CAST("F_AA_LOAN_DISBURSE"."DISB_DATE" AS CHAR) FROM 1 FOR 4)

    111.jpg

    i want 2015/07/31. please help me,

    Tahnks in advance,

    a.kavya

  • Niamh O
    Niamh O Rank 2 - Community Beginner

    Hi Kavya

    Can you try with a brand new column with no special/custom data formatting? (use an existing string type column and change the formula)

    If this doesn't fix, troubleshoot in the RPD:

    What is the physical data column type of this field? Is it an Oracle table?

    It has to be a DATE type for the above to work. And classified as a DATE or DATETIME in the RPD physical column attributes.

    Is it being formatted anywhere in the physical or logical layers in the RPD?

    Let me know - Niamh

  • Niamh O
    Niamh O Rank 2 - Community Beginner

    2016-01-22 11_17_07-Oracle BI Answers.png

  • Niamh O
    Niamh O Rank 2 - Community Beginner

    fyi: these have identical formulas inside!

  • Hi,

    If i use current_date, its working fine. If i use our table date like

    CAST( SUBSTRING(CAST("F_PL_BAL"."BUSINESS_DATE" AS CHAR) FROM 1 FOR 4) ||  '/07/31/'  AS DATE)

    i am getting below error.

    View Display Error

    Odbc driver returned an error (SQLExecDirectW).

      Error Details

    Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P

    State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 17001] Oracle Error code: 1843, message: ORA-01843: not a valid month at OCI call OCIStmtFetch. [nQSError: 17012] Bulk fetch failed. (HY000)

    SQL Issued: SELECT 0 s_0, CAST(SUBSTRING(CAST("FIN_016"."F_PL_BAL"."BUSINESS_DATE" AS CHAR) FROM 1 FOR 4)||'/07/31/' AS DATE) s_1 FROM "FIN_016" FETCH FIRST 65001 ROWS ONLY

    Refresh

    13.jpg

    Please help me,

    Thanks in advance,

    A.kavya.

  • Niamh O
    Niamh O Rank 2 - Community Beginner

    The below is based on my source Oracle database NLS date format as DD-MON-RR (Query on database- SELECT * > FROM nls_database_parameters WHERE PARAMETER = 'NLS_DATE_FORMAT').

    Note this is not altered by the RPD connection pool, which is common.

    So the date I assemble for Substrings and Casts has to match that before it can be Re-CAST as a DATE. ie 31-JUL-15 etc


    Steps:

    - drag a text field onto the report, any one

    - Edit formula

    - Paste in

    CAST ( '31-Jul-'|| SUBSTRING( CAST( "F_PL_BAL"."BUSINESS_DATE" AS CHAR) FROM 8 FOR 2) AS DATE)

    - OK

    - View results


  • cesar.advincula.o
    cesar.advincula.o Rank 6 - Analytics Lead

    char_to_date.jpg

    - TO_DATETIME('2009-DEC-03', 'YYY...    ->

       Formula:  TO_DATETIME('2009-DEC-03', 'YYYY-MON-DD')

    - FirstDayCharDateNoConver                     ->

      Formula:  CONCAT('01-' , UPPER("FecCobro"."MesAñoCobro" ))


    - MesCharConvert                                     ->

      Formula: 

                          CASE WHEN SUBSTRING("FecCobro"."MesAñoCobro"  FROM 0 FOR 3)= 'Ene'  THEN 'Jan' 

                                    WHEN SUBSTRING("FecCobro"."MesAñoCobro"  FROM 0 FOR 3)  = 'Abr'  THEN 'Apr' 

                                     WHEN SUBSTRING("FecCobro"."MesAñoCobro"  FROM 0 FOR 3)  = 'Dic'  THEN 'Dec'

                           ELSE SUBSTRING("FecCobro"."MesAñoCobro"  FROM 0 FOR 3)  END


    - CharDateConvert                                    -> Formula:

    CONCAT ( CONCAT('01-' , CASE WHEN SUBSTRING("FecCobro"."MesAñoCobro"  FROM 0 FOR 3)= 'Ene'  THEN 'Jan' 

                                                       WHEN SUBSTRING("FecCobro"."MesAñoCobro"  FROM 0 FOR 3)  = 'Abr'  THEN 'Apr' 

                                                       WHEN SUBSTRING("FecCobro"."MesAñoCobro"  FROM 0 FOR 3)  = 'Dic'  THEN 'Dec'

                                                 ELSE SUBSTRING("FecCobro"."MesAñoCobro"  FROM 0 FOR 3)  END) ,

                                                             CONCAT( '-',SUBSTRING("FecCobro"."MesAñoCobro"  FROM 5 FOR 4))   )


    - FirstDateFromPeriodChar    ->

      Formula: 

    TO_DATETIME(TRIM(CONCAT ( CONCAT('01-' , CASE WHEN SUBSTRING("FecCobro"."MesAñoCobro"  FROM 0 FOR 3)= 'Ene'  THEN 'Jan' 

                                                                                         WHEN SUBSTRING("FecCobro"."MesAñoCobro"  FROM 0 FOR 3)  = 'Abr'  THEN 'Apr'

                                                                                         WHEN SUBSTRING("FecCobro"."MesAñoCobro"  FROM 0 FOR 3)  = 'Dic'  THEN 'Dec'

                                                              ELSE SUBSTRING("FecCobro"."MesAñoCobro"  FROM 0 FOR 3)  END) ,

                                                                          CONCAT( '-',SUBSTRING("FecCobro"."MesAñoCobro"  FROM 5 FOR 4))   )) , 'DD-Mon-YYYY')



    - LastDayCurrentMonth    ->

       Formula: 

      TIMESTAMPADD( SQL_TSI_DAY , -(1), TIMESTAMPADD( SQL_TSI_MONTH , 1, TIMESTAMPADD( SQL_TSI_DAY , DAYOFMONTH( CURRENT_DATE) * -(1) + 1, CURRENT_DATE)))



  • Niamh O
    Niamh O Rank 2 - Community Beginner

    A dedicated DATE dimension table would bring a lot of value in this case, all date fields you need relative to a day come ready made into queries, and be worth the development and RPD modeling.

    Using star schema rules will get the best result out of OBIEE.

    Very good luck exploring a solution!

  • Hi,

    sorry for late reply,

    as you said, i have tried on that day, its working fine..

    I have used like this formulae

    CAST('31-Jul-' || TRIM(BOTH ' ' FROM  CAST( YEAR("F_PL_BAL"."BUSINESS_DATE")       AS CHAR) ) AS DATE)

    Thank you very much,


    A.kavya