10 Replies Latest reply: Jan 11, 2013 8:32 AM by BluShadow RSS

    Issues with TO_DATE conversion

    Subhasish
      Hi All,

      We are migrating from 11i to R12 environment. During the migration we are facing some issue with TO_DATE conversion. The below code is working fine in 11i environment whereas failing in R12.
      SELECT SIGN(TO_DATE(NVL(pac.segment6, pac.segment5),'YYYY/MM/DD HH24:MI:SS')-SYSDATE)   sign
      ,DECODE(SIGN(TO_DATE(NVL(pac.segment6, pac.segment5),'YYYY/MM/DD HH24:MI:SS')-SYSDATE),-1,'Visa Expired',
      'Visa Due to Expire in Next ' || :p_no_of_days || ' Days')  expired
      ,      papf.last_name || ', ' || papf.first_name full_name
      ,      papf.employee_number
      ,      TO_DATE(NVL(pac.segment6, pac.segment5),'YYYY/MM/DD HH24:MI:SS') expiry_date
      ,      haou.name            dept
      ,      SUBSTR(hapf.name,1,INSTR(hapf.name,'.') - 1)            position
      ,      papf1.last_name || ', ' || papf1.first_name  supervisor_name
      FROM   per_all_people_f papf1
      ,      hr_all_positions_f        hapf
      ,      hr_all_organization_units    haou
      ,      per_all_assignments_f  paaf
      ,      per_all_people_f papf
      ,      per_analysis_criteria pac
      ,      per_person_analyses   ppa
      ,      fnd_id_flex_structures fifs
      WHERE  fifs.id_flex_structure_code = 'XX_VISA'
      AND    fifs.id_flex_code = 'PEA'
      AND    fifs.id_flex_num = ppa.id_flex_num
      AND    ppa.analysis_criteria_id = pac.analysis_criteria_id
      --   MAX for expiry date 
      AND    NVL(pac.segment6, pac.segment5) = 
             (SELECT MAX(NVL(pac.segment6,pac1.segment5))
               FROM   per_analysis_criteria pac1
               ,      per_person_analyses   ppa1
               WHERE  ppa1.person_id = ppa.person_id
               AND    ppa1.id_flex_num = ppa.id_flex_num
               AND    ppa1.analysis_criteria_id = pac1.analysis_criteria_id)
      AND    ppa.person_id = papf.person_id
      AND    TRUNC(sysdate) BETWEEN papf.effective_start_date AND papf.effective_end_date
      AND    papf.current_employee_flag = 'Y'
      AND    papf.person_id = paaf.person_id
      AND    TRUNC(sysdate) BETWEEN paaf.effective_start_date AND paaf.effective_end_date
      AND    paaf.assignment_type = 'E'
      AND    paaf.organization_id = haou.organization_id 
      AND    paaf.position_id = hapf.position_id(+)
      AND    TRUNC(sysdate) BETWEEN hapf.effective_start_date(+) AND hapf.effective_end_date(+)
      AND    paaf.supervisor_id = papf1.person_id(+)
      AND    TRUNC(sysdate) BETWEEN papf1.effective_start_date(+) AND papf1.effective_end_date(+)
      AND    TO_DATE(NVL(pac.segment6, pac.segment5),'YYYY/MM/DD HH24:MI:SS') < sysdate + :p_no_of_days
      ORDER BY SIGN(TO_DATE(NVL(pac.segment6, pac.segment5),'YYYY/MM/DD HH24:MI:SS')-SYSDATE)
      ,      TO_DATE(NVL(pac.segment6, pac.segment5),'YYYY/MM/DD HH24:MI:SS') 
      ,     papf.employee_number
      The issue is being faced at
      AND TO_DATE(NVL(pac.segment6, pac.segment5),'YYYY/MM/DD HH24:MI:SS') < sysdate + :p_no_of_days

      :p_no_of_days can be set to 0.

      The datatype of segment5 and segment6 is VARCHAR2 and the data is in the form of 'YYYY/MM/DD HH24:MI:SS'
      e.g. 2010/11/30 00:00:00
      2011/01/08 00:00:00 etc

      The error we are getting is : ORA-01861: literal does not match format string. Can anyone help.
        • 1. Re: Issues with TO_DATE conversion
          BluShadow
          Subhasish wrote:
          The datatype of segment5 and segment6 is VARCHAR2 and the data is in the form of 'YYYY/MM/DD HH24:MI:SS'
          e.g. 2010/11/30 00:00:00
          2011/01/08 00:00:00 etc

          The error we are getting is : ORA-01861: literal does not match format string. Can anyone help.
          Looks like you have some data in segment5 or segment6 that is NOT of the format YYYY/MM/DD HH24:MI:SS.

          All the more reason that dates should not be stored in VARCHAR2 datatypes.
          • 2. Re: Issues with TO_DATE conversion
            AlbertoFaenza
            Hi,

            I don't think that the error is caused due to a migration. It looks that the argument that your are passing to the TO_DATE function doesn't match your format string.

            i.e.:
            select TO_DATE('10/01/2012 14:52:00', 'YYYY/MM/DD HH24:MI:SS') from dual;
                           *
            Error at line 1
            ORA-01861: literal does not match format string
            Since we don't have your sample data it's not possible to understand the problem.

            Regards.
            Al
            • 3. Re: Issues with TO_DATE conversion
              mtefft
              I agree that you must have some non-compliant data. I suggest you try some analysis along these lines:
              select substr(pac.segment5,1,4) as segment5_yyyy, count(*), min(pac.segment5), max(pac.segment5) 
              from per_analysis_criteria pac group by substr(pac.segment5,1,4);
              
              select substr(pac.segment6,1,4) as segment6_yyyy, count(*), min(pac.segment6), max(pac.segment6) 
              from per_analysis_criteria pac group by substr(pac.segment6,1,4);
              
              select substr(pac.segment5,6,2) as segment5_mm, count(*), min(pac.segment5), max(pac.segment5) 
              from per_analysis_criteria pac group by substr(pac.segment5,6,2);
              
              select substr(pac.segment6,6,2) as segment6_mm, count(*), min(pac.segment6), max(pac.segment6) 
              from per_analysis_criteria pac group by substr(pac.segment6,6,2);
              
              etc.
              select substr(pac.segment5,5,1)|| substr(pac.segment5,8,1) || substr(pac.segment5,11,1) || substr(pac.segment5,14,1) || substr(pac.segment5,17,1) as segment5_literals, 
              count(*), min(pac.segment5), max(pac.segment5) 
              from per_analysis_criteria pac 
              group by substr(pac.segment5,5,1)|| substr(pac.segment5,8,1) || substr(pac.segment5,11,1) || substr(pac.segment5,14,1) || substr(pac.segment5,17,1);
              I like to use the min() and max() to get 2 samples of data that contain each fragment value - without having to re-query the data.
              Somewhere in there, you have something that's not right.

              Mike
              • 4. Re: Issues with TO_DATE conversion
                Subhasish
                Hi All,

                Thank you for the suggestions.

                The table per_analysis_criteria is a seeded table, where the datatype of segment1 .. segment30 is varchar2(150). The data is stored in the table via Oracle forms (HRMS -> Enter and Maintain -> Special Information Types). The configuration is done via KFF Personal Analysis Criteria.

                Segment5 and Segment6 contains all kinds of data along with the data stored as varchar. We filter the data rows based on particular setup. XX_VISA in this case. The filtered data set contains only data in the date format.
                This query is working fine in our live 11i environment, however failing in its cloned r12 environment.
                • 5. Re: Issues with TO_DATE conversion
                  Subhasish
                  Hi All,

                  We managed it !!!

                  Used "(SELECT TO_DATE(NVL(pac.segment6, pac.segment5),'YYYY/MM/DD HH24:MI:SS') FROM dual) < SYSDATE + :p_no_of_days" in the condition.

                  Thanks for all the inputs...
                  • 6. Re: Issues with TO_DATE conversion
                    BluShadow
                    Subhasish wrote:
                    Hi All,

                    Thank you for the suggestions.

                    The table per_analysis_criteria is a seeded table, where the datatype of segment1 .. segment30 is varchar2(150). The data is stored in the table via Oracle forms (HRMS -> Enter and Maintain -> Special Information Types). The configuration is done via KFF Personal Analysis Criteria.

                    Segment5 and Segment6 contains all kinds of data along with the data stored as varchar.
                    A good example of a very poorly designed database.

                    It deserves to have cr@p data in it.
                    • 7. Re: Issues with TO_DATE conversion
                      mtefft
                      So the problem is that you have some rows with non-compliant data, but you expect to exclude them via other predicates. But the predicates were not being evaluated in an order that was convenient. It appears that you have landed on a query that presently evaluates them in an order that works for you. But your code does not guarantee that such an order is permanent.

                      To be sure you permanently address this issue, you should write your query to ensure it evaluates the relevant conditions in the order that will avoid errors. One technique that explicitly controls order of evaluation is to use CASE. And I suggest you encapsulate this mess in a view, or at least a factored subquery (i.e. WITH ).

                      Your view could have a column like this:
                      SELECT
                        CASE WHEN (THIS_ROW_HAS_A_DATE = 'N') THEN
                               CAST(NULL AS DATE)
                             ELSE 
                               TO_DATE(NVL(pac.segment6, pac.segment5),'YYYY/MM/DD HH24:MI:SS')
                        END AS SEGMENT_6_OR_5_DATE
                      [remainder of query]
                      Of course, substitute whatever the true logic is for "THIS_ROW_HAS_A_DATE = 'N'".

                      If you don't do something to specifically address this issue, then you will be riding on luck until your execution plan changes to something that is less cooperative.
                      (format changes)

                      Edited by: mtefft on Jan 11, 2013 8:17 AM
                      • 8. Re: Issues with TO_DATE conversion
                        BluShadow
                        mtefft wrote:
                        To be sure you permanently address this issue, you should write your query to ensure it evaluates the relevant conditions in the order that will avoid errors.
                        I would have said "To be sure you permanently address this issue, scrap the rubbish database design that attempts to store all data in generic varchar2 columns, and design the database properly using known columns and datatypes with proper relational tables".

                        ;)
                        • 9. Re: Issues with TO_DATE conversion
                          John Spencer
                          BluShadow wrote:
                          mtefft wrote:
                          To be sure you permanently address this issue, you should write your query to ensure it evaluates the relevant conditions in the order that will avoid errors.
                          I would have said "To be sure you permanently address this issue, scrap the rubbish database design that attempts to store all data in generic varchar2 columns, and design the database properly using known columns and datatypes with proper relational tables".

                          ;)
                          Are you critcizing the developers at Oracle's EBS group? :-) The tables are from Peoplesoft as I recall.

                          John
                          • 10. Re: Issues with TO_DATE conversion
                            BluShadow
                            John Spencer wrote:
                            BluShadow wrote:
                            mtefft wrote:
                            To be sure you permanently address this issue, you should write your query to ensure it evaluates the relevant conditions in the order that will avoid errors.
                            I would have said "To be sure you permanently address this issue, scrap the rubbish database design that attempts to store all data in generic varchar2 columns, and design the database properly using known columns and datatypes with proper relational tables".

                            ;)
                            Are you critcizing the developers at Oracle's EBS group? :-) The tables are from Peoplesoft as I recall.

                            John
                            Would I?

                            We used to have a peoplesoft database... 'nuf said. ;)