Solved

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

Posted on 2014-04-15
24
7,732 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 32

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 32

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 32

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 32

Expert Comment

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

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

910 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

21 Experts available now in Live!

Get 1:1 Help Now