Oracle Analytics

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics

Categories

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
3221
Views
10
Comments
ARIVEMULA KAVYA KUMARI, LERA TECHNOLOGY PVT LTD
ARIVEMULA KAVYA KUMARI, LERA TECHNOLOGY PVT LTD Rank 5 - Community Champion
edited Aug 13, 2024 7:09PM in Oracle Analytics

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

  • ARIVEMULA KAVYA KUMARI, LERA TECHNOLOGY PVT LTD
    ARIVEMULA KAVYA KUMARI, LERA TECHNOLOGY PVT LTD Rank 5 - Community Champion

    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!

  • ARIVEMULA KAVYA KUMARI, LERA TECHNOLOGY PVT LTD
    ARIVEMULA KAVYA KUMARI, LERA TECHNOLOGY PVT LTD Rank 5 - Community Champion

    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 & AI 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!

  • ARIVEMULA KAVYA KUMARI, LERA TECHNOLOGY PVT LTD
    ARIVEMULA KAVYA KUMARI, LERA TECHNOLOGY PVT LTD Rank 5 - Community Champion

    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