0

I'm working in an aviation environment where I have to compare two values. The OEM in all its wisdom has stored all datetime values in char. The values to compare are called, say, aibt and sibt As part of the where clause I need to check if aibt is greater than sibt. So this is what I did :

To_Date(aibt,'YYYYMMDDHH24MISS') > To_Date(sibt,'YYYYMMDDHH24MISS')

When applying to_date, the original char values are converted to : 25.12.2013 12:54:00

But I get the following error :

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

Mihai
  • 26,325
  • 7
  • 66
  • 81
bijeshn
  • 43
  • 8

3 Answers3

1

Seems like an error in your data. Try this first:

Select to_date(aibt,'YYYYMMDDHH24MISS') from your_table

Select to_date(sibt,'YYYYMMDDHH24MISS') from your_table

to check if you have some incorrect data in your columns.

When you identified your problematic data in your column, you could find the data that is messing this up with somethin like this: 

select *
  from yourtable
 where not regexp_like(aibdt, '^[[:digit:]]+$') 
  OR length(aibdt)<4 OR 
   substr(aibdt,1,4)<0
Aitor
  • 3,309
  • 2
  • 27
  • 32
1

You obviously have some data that can not be converted with TO_DATE to a proper date. Feel free to berate your OEM for not defining constraints or otherwise prevent this from happening.

While he gets around to fix his problems, you can use this as a workaround:

WHERE aibt > sibt
Twinkles
  • 1,984
  • 1
  • 17
  • 31
0

There was some problem in the data. Also thanks Twinkles for your contribution. I hadnt thought of that. Can anyone explain how comparing two char values work? I got the answer (what I'd really required) in another post Oracle: how to add minutes to a timestamp? which I used for constructing the comparison :

WHERE To_Date(aibt,'YYYYMMDDHH24MISS')>To_Date(sibt,'YYYYMMDDHH24MISS')+ INTERVAL '15' MINUTE

Thanks everyone!

Community
  • 1
  • 1
bijeshn
  • 43
  • 8