1 2 Previous Next 22 Replies Latest reply: Dec 5, 2012 3:05 AM by Kumar Kasinathan RSS

    Problem while converting varchar2 datatype to date

    Kumar Kasinathan
      Hi All,

      I am facing a problrm while writing a query to extract a SIT detail of all employees in which i am comparing the "sysdate" with a "varchar2" type column of the SIT table. I am getting the following error ORA-01841: (full) year must be between -4713 and +9999, and not be 0 and the query is
      ++++++++++++++++++++
      SELECT papf.person_id,
      papf.employee_number,
      papf.full_name,
      pac.segment2 work_permit_type,
      pac.segment7 visa_expiry_date
      FROM per_all_people_f papf,
      per_person_analyses ppa,
      per_analysis_criteria pac,
      fnd_id_flex_structures_tl fifst,
      fnd_id_flex_structures fifs
      WHERE papf.person_id = ppa.person_id
      AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date
      AND papf.effective_end_date
      AND papf.current_employee_flag = 'Y'
      AND ppa.analysis_criteria_id = pac.analysis_criteria_id
      AND pac.id_flex_num = fifs.id_flex_num
      AND fifst.id_flex_structure_name='THE SIT NAME'
      AND fifst.language=USERENV('LANG')
      AND fifst.application_id=fifs.application_id
      AND fifst.id_flex_num=fifs.id_flex_num
      AND TO_DATE(pac.segment7,'YYYY/MM/DD HH24:MI:SS') >= TRUNC(SYSDATE)
      AND papf.employee_number='9127327':
      +++++++++++++++++++++++++++++++++++++

      The strange thing is the above query is working fine witout the last filter condition(AND papf.employee_number='9127327') but throws the mentioned error i.e ORA-01841: (full) year must be between -4713 and +9999, and not be 0 with the condition.

      Could any one help me on this?

      Edited by: Kumar Kasinathan on 04-Dec-2012 02:58
        • 1. Re: Problem while converting varchar2 datatype to date
          Hoek
          What value does pac.segment7 return when papf.employee_number='9127327'?
          (Omit the to_date part, for testing purposes)
          It looks like that value cannot be converted into a valid date value and you'll need to work around that or update the value.
          And what is the datatype of employee_number, by the way? Is it really a string/varchar2?
          • 2. Re: Problem while converting varchar2 datatype to date
            LPS
            AND TO_DATE(pac.segment7,'YYYY/MM/DD HH24:MI:SS') >= TRUNC(SYSDATE)

            Firrst check the format of SYSDATE. Here you misplaced year in the first position instead of last position. First check the format before comparing by converting

            Comment each condition and try to identify which condition going wrong...thats the way for debugging by yourself
            • 3. Re: Problem while converting varchar2 datatype to date
              BluShadow
              LPS wrote:
              AND TO_DATE(pac.segment7,'YYYY/MM/DD HH24:MI:SS') >= TRUNC(SYSDATE)

              Firrst check the format of SYSDATE. Here you misplaced year in the first position instead of last position. First check the format before comparing by converting
              What utter nonsense.

              SYSDATE doesn't have a 'format'. It's a DATE datatype, so there is no positioning of the year being in the first, last, middle, or anywhere else you'd like to shove it.

              The error message clearly indicates an issue with the "TO_DATE(pac.segment7,'YYYY/MM/DD HH24:MI:SS')" statement, and if that error is only becoming apparent when the records are further restricted in the query, then that leads me to believe that when that restriction is not in place, not all the data is being retrieved in the first instance, so likely it's being run in a tool like TOAD or something that is only fetching back the first 500 rows, which probably don't have corrupt data. When the additional restriction is put in place it's bringing the corrupt data to the first set of rows returned and so the error becomes apparent.

              The first thing to do is identify what the datatype of the column pac.segment7 is. The OP tells us this is varchar2, but we could do with that confirming to be sure.

              If it is varchar2, then quite simply the data for that record is not in the format YYYY/MM/DD HH24:MI:SS.
              If it is not a varchar2, and is actually a DATE, then there is implicit conversion going on which could cause the issue.
              • 4. Re: Problem while converting varchar2 datatype to date
                Kumar Kasinathan
                The "segment7" column is varchar2 type only and it holds the value as "2013/06/14 00:00:00" .
                The employee_number is also varchar2 type.
                • 5. Re: Problem while converting varchar2 datatype to date
                  LPS
                  Check your date format before comparing with sysdate.

                  SQL > SELECT SYSDATE FROM DUAL;

                  And then use the format specifiers according to, in the comparison for segment7 to with sysdate.

                  Make sure all the data in segment7 are in the same format.
                  • 6. Re: Problem while converting varchar2 datatype to date
                    jeneesh
                    LPS wrote:
                    Check your date format before comparing with sysdate.

                    SQL > SELECT SYSDATE FROM DUAL;

                    And the use the format specifiers according to in the comparison for segment7 to with sysdate
                    Did you read Blushadow's reply to your first post...
                    • 7. Re: Problem while converting varchar2 datatype to date
                      Kumar Kasinathan
                      Hi BluShadow,

                      Thanks for your update.

                      Yes you are right the query is executed in TOAD. But the total record for this query is less than 500 and i am sure that pac.segment7 column is varchar2 type.
                      • 8. Re: Problem while converting varchar2 datatype to date
                        BluShadow
                        LPS wrote:
                        Check your date format before comparing with sysdate.

                        SQL > SELECT SYSDATE FROM DUAL;

                        And then use the format specifiers according to, in the comparison for segment7 to with sysdate.

                        Make sure all the data in segment7 are in the same format.
                        Again you're talking nonsense.

                        The format of the varchar2 data is not related to the nls_date_format setting that is used to display the sysdate...
                        SQL> select sysdate from dual;
                        
                        SYSDATE
                        -------------------
                        04/12/2012 10:53:04
                        
                        SQL>
                        SQL> select 1 from dual where to_date('2012/12/04','YYYY/MM/DD') = trunc(sysdate);
                        
                                 1
                        ----------
                                 1
                        See... there was no need to specify the date format in the to_date function to match the format that sysdate was displayed in.

                        The only way the error will occur is if the varchar2 data is corrupt in some way...
                        SQL> ed
                        Wrote file afiedt.buf
                        
                          1* select 1 from dual where to_date('0000/12/04','YYYY/MM/DD') = trunc(sysdate)
                        SQL> /
                        select 1 from dual where to_date('0000/12/04','YYYY/MM/DD') = trunc(sysdate)
                                                         *
                        ERROR at line 1:
                        ORA-01841: (full) year must be between -4713 and +9999, and not be 0
                        • 9. Re: Problem while converting varchar2 datatype to date
                          BluShadow
                          Kumar Kasinathan wrote:
                          The "segment7" column is varchar2 type only and it holds the value as "2013/06/14 00:00:00" .
                          The employee_number is also varchar2 type.
                          Well, if that really is the data for that record then it should work ok (without error)...
                          SQL> ed
                          Wrote file afiedt.buf
                          
                            1* select 1 from dual where to_date('2013/06/14 00:00:00','YYYY/MM/DD HH24:MI:SS') = trunc(sysdate)
                          SQL> /
                          
                          no rows selected
                          Are there any other characters in that varchar2 data?

                          select dump(segment7) from your table for that record to get a look at the bytes it contains.
                          • 10. Re: Problem while converting varchar2 datatype to date
                            Kumar Kasinathan
                            Thanks again BluSahdow,

                            The dump function returs same lengt i.e "Typ=1 Len=19:" for all records.
                            • 11. Re: Problem while converting varchar2 datatype to date
                              BluShadow
                              Kumar Kasinathan wrote:
                              Thanks again BluSahdow,

                              The dump function returs same lengt i.e "Typ=1 Len=19:" for all records.
                              But what about the data?

                              The error is saying that the data does not meet the format you have specified in your TO_DATE function.
                              We don't have your data, we can only clarify for you what the error means and explain where to look.

                              If you are telling us that the data is varchar2 and is definitely the string '2013/06/14 00:00:00' then it will convert to date ok. However, Oracle it saying that you have corrupt data there. So, it's now up to you to track down that corrupt data.
                              • 12. Re: Problem while converting varchar2 datatype to date
                                BluShadow
                                Another possibility:
                                AND TRUNC (SYSDATE) BETWEEN papf.effective_start_date
                                AND papf.effective_end_date
                                What are the datatypes of effective_start_date and effective_end_date?

                                Are these DATE or VARCHAR2?
                                • 13. Re: Problem while converting varchar2 datatype to date
                                  Kumar Kasinathan
                                  The effective_start_date and effective_end_date are date type.

                                  Sorry for the wrong information...

                                  the pac.segment7 is having '2013/08/18 00:00:00' only for the SIT 'Work Permits' ( "AND fifst.id_flex_structure_name='Work Permits") for other SITs the format differs as 'E', '10' , '23.05' like that.

                                  In the tabel per_analysis_criteria, column segment7 is having different sort of value format like 'E','4.5' etc, but for the particular SIT i.e 'Work Permits' it is having the format '2013/08/18 00:00:00'.

                                  We are restricting it using join condition in the query ( "AND fifst.id_flex_structure_name='Work Permits").

                                  It seems the to_date conversion is taking place before the SIT filter condition beeing effected, so it takes all fomat values of column pac.segment7 and try to convert them into date formate and so we are getting the error.

                                  So i have to concentrate on flow of query execution to restrict it from extracting all segment7 values and then comparing it to filter with particular SIT and i am not sure how to do it.


                                  Thanks...
                                  • 14. Re: Problem while converting varchar2 datatype to date
                                    Kumar Kasinathan
                                    Thanks Hoek,

                                    Without the date condition it returns "2013/03/28 00:00:00" and the employee_number is the varchar2 type.
                                    1 2 Previous Next