Solved

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

Posted on 2014-04-15
24
8,181 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 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
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 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

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

Suggested Solutions

Title # Comments Views Activity
Oracle - SQL Parse String 5 45
best datatype for oracle table email creation 8 56
make null the repeated levels 2 31
Oracle Nested table uses ? 2 34
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 video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to take different types of Oracle backups using RMAN.

829 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