Solved

ORA-01841: (full) year must be between -4713 and +9999

Posted on 2014-04-15
24
7,488 Views
Last Modified: 2014-04-15
Hi guys,

I have the following SQL:
Select field1, field2, field3, additional_info
from table1
where to_date(substr(additional_info,4,10), 'yyyy/mm/dd') > '01-apr-2013'

I receive the message "ORA-01841: (full) year must be between -4713 and +9999"
The field "additional_info is a varchar date type and all records start with  'AB:2012/03/13 '.............
Can anyone help me?

Thank you
0
Comment
Question by:marian68
  • 9
  • 7
  • 5
  • +1
24 Comments
 
LVL 37

Expert Comment

by:Gerwin Jansen
ID: 40001641
Can you try:

where to_date(substr(additional_info,4,10), 'yyyy/mm/dd') > '2013/04/01'
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 250 total points
ID: 40001654
don't compare dates to strings

compare dates to dates

to_date(substr(additional_info,4,10), 'yyyy/mm/dd') > to_date('01-apr-2013','dd-mon-yyyy')
0
 

Author Comment

by:marian68
ID: 40001655
Same message,

Thank you
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 40001659
which did you try?
0
 

Author Comment

by:marian68
ID: 40001670
I tried only the 1st answer and it was highlighted the string
 'yyyy/mm/dd'
0
 

Author Comment

by:marian68
ID: 40001674
I tried the second answer .
The same message.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 40001677
what are the results of trying the 2nd answer?
0
 

Author Comment

by:marian68
ID: 40001693
Same message.

Thank you
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 40001700
ok, then your data is not in the format you think it is.

Try using this to find the erroneous rows

select * from table1 where NOT REGEXP_LIKE(additional_info, 'AB:[0-9]{4}/[0-9]{2}/[0-9]{2}.*')
0
 
LVL 31

Assisted Solution

by:awking00
awking00 earned 250 total points
ID: 40001702
What does this query produce?
select additional_info
from table1
where to_number(substr(additional_info,4,4)) not between -4713 and 9999
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 250 total points
ID: 40001723
or, if the "AB:" prefix might vary try this to find the erroneous dates


SELECT *
  FROM table1
 WHERE NOT REGEXP_LIKE(SUBSTR(additional_info, 4, 10), '^[0-9]{4}/[0-9]{2}/[0-9]{2}$')
0
 

Author Comment

by:marian68
ID: 40001742
for sdstuber:

I run the sql you gave me and I got only 1 record where in additional info is not any date.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:marian68
ID: 40001762
for awking00:

I run your sql and I got the error message "ORA-01722: invalid number" and the table is highlighted even if it is the correct table.

Thank you
0
 
LVL 31

Assisted Solution

by:awking00
awking00 earned 250 total points
ID: 40001776
Then there is a non-numeric character where you expect the year to be.
select additional_info from table1
where not regexp_like(substr(additional_info,4,4),'([[:digit:]])');
0
 
LVL 37

Expert Comment

by:Gerwin Jansen
ID: 40001786
Can you post the output of this (exactly if possible):

select field1, field2, field3, additional_info
from table1
where rownum=1;

That way we can see what the 'real' data looks like.
0
 
LVL 31

Expert Comment

by:awking00
ID: 40001793
That's not quite the right regular expression. You could use substr(additional_info,4,1), then (5,1),(6,1), and (7,1) but I'm sure there is a better one. Will work on it.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 40001804
>>>I run the sql you gave me and I got only 1 record where in additional info is not any date.

One row is sufficient to produce the error

Either fix that data if it's invalid,  or, add to your where clause so the invalid data will be skipped
0
 

Author Comment

by:marian68
ID: 40001818
for awking00:

I run your last query and I found 2 records.
0
 
LVL 31

Expert Comment

by:awking00
ID: 40001822
select * from table1
where regexp_like(substr('Additional_Info',4,4),'[^0-9]+');
0
 
LVL 31

Expert Comment

by:awking00
ID: 40001829
What were the two values you found for the additional_info field?
0
 

Author Comment

by:marian68
ID: 40001863
For awking00:

Sorry I didn't mention but in the additional info wasn't any date so I think I have to eliminate this 2 records from my query.

Thank you
0
 
LVL 37

Expert Comment

by:Gerwin Jansen
ID: 40001870
>> in the additional info wasn't any date
If there's no date and you parse that string with to_date and YYYY then the Oracle message is correct.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 40001896
>>>  so I think I have to eliminate this 2 records from my query.


That's what I said earlier

you either have to fix the data if it's invalid, or add to your where clause so those rows are skipped
0
 

Author Closing Comment

by:marian68
ID: 40001900
Thank you guys,

I think the SQL is working in its simplest form.
I will make the adjustments in the original bigger query to find out if everything is OK.

Thank you again,
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now