This discussion is archived
1 2 Previous Next 22 Replies Latest reply: Dec 5, 2012 1:05 AM by 947354 RSS

Problem while converting varchar2 datatype to date

947354 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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
    947354 Newbie
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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
    947354 Newbie
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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
    947354 Newbie
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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 Guru Moderator
    Currently Being Moderated
    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
    947354 Newbie
    Currently Being Moderated
    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
    947354 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points