• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 10204
  • Last Modified:

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

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
marian68
Asked:
marian68
  • 9
  • 7
  • 5
  • +1
4 Solutions
 
Gerwin Jansen, EE MVETopic Advisor Commented:
Can you try:

where to_date(substr(additional_info,4,10), 'yyyy/mm/dd') > '2013/04/01'
0
 
sdstuberCommented:
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
 
marian68Author Commented:
Same message,

Thank you
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
sdstuberCommented:
which did you try?
0
 
marian68Author Commented:
I tried only the 1st answer and it was highlighted the string
 'yyyy/mm/dd'
0
 
marian68Author Commented:
I tried the second answer .
The same message.
0
 
sdstuberCommented:
what are the results of trying the 2nd answer?
0
 
marian68Author Commented:
Same message.

Thank you
0
 
sdstuberCommented:
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
 
awking00Commented:
What does this query produce?
select additional_info
from table1
where to_number(substr(additional_info,4,4)) not between -4713 and 9999
0
 
sdstuberCommented:
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
 
marian68Author Commented:
for sdstuber:

I run the sql you gave me and I got only 1 record where in additional info is not any date.
0
 
marian68Author Commented:
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
 
awking00Commented:
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
 
Gerwin Jansen, EE MVETopic Advisor Commented:
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
 
awking00Commented:
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
 
sdstuberCommented:
>>>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
 
marian68Author Commented:
for awking00:

I run your last query and I found 2 records.
0
 
awking00Commented:
select * from table1
where regexp_like(substr('Additional_Info',4,4),'[^0-9]+');
0
 
awking00Commented:
What were the two values you found for the additional_info field?
0
 
marian68Author Commented:
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
 
Gerwin Jansen, EE MVETopic Advisor Commented:
>> 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
 
sdstuberCommented:
>>>  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
 
marian68Author Commented:
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 9
  • 7
  • 5
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now