Solved

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

Posted on 2014-04-15
24
8,896 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 7
  • 5
  • +1
24 Comments
 
LVL 38

Expert Comment

by:Gerwin Jansen, EE MVE
ID: 40001641
Can you try:

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

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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
LVL 74

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 74

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 74

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 74

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
 

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 38

Expert Comment

by:Gerwin Jansen, EE MVE
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 74

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 38

Expert Comment

by:Gerwin Jansen, EE MVE
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 74

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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

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.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not 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
Suggested Courses

627 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