10 Replies Latest reply on Jan 27, 2016 4:21 AM by ARIVEMULA KAVYA KUMARI, LERA TECHNOLOGY PVT LTD

    Convert char to date format in obiee 11g?

    ARIVEMULA KAVYA KUMARI, LERA TECHNOLOGY PVT LTD

      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.

        • 1. Re: Convert char to date format in obiee 11g?
          Niamh O

          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

          • 2. Re: Convert char to date format in obiee 11g?
            ARIVEMULA KAVYA KUMARI, LERA TECHNOLOGY PVT LTD

            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

            • 3. Re: Convert char to date format in obiee 11g?
              Niamh O

              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

              • 5. Re: Convert char to date format in obiee 11g?
                Niamh O

                fyi: these have identical formulas inside!

                • 6. Re: Convert char to date format in obiee 11g?
                  ARIVEMULA KAVYA KUMARI, LERA TECHNOLOGY PVT LTD

                  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).

                  http://192.168.0.165:9704/analytics/res/v-ISAcoW1LJVY/sk_blafp/common/errorminus.gif  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.

                  • 7. Re: Convert char to date format in obiee 11g?
                    Niamh O

                    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


                    1 person found this helpful
                    • 8. Re: Convert char to date format in obiee 11g?
                      cesar.advincula.o

                      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)))

                       



                      1 person found this helpful
                      • 9. Re: Convert char to date format in obiee 11g?
                        Niamh O

                        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!

                        • 10. Re: Convert char to date format in obiee 11g?
                          ARIVEMULA KAVYA KUMARI, LERA TECHNOLOGY PVT LTD

                          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

                          1 person found this helpful