Solved

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

Posted on 2014-04-15
24
7,940 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
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.

 
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
 

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

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.

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…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
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
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

776 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