oracle错误代码1840,Strange Oracle error 1840 - input value not long enough for date format

Hi.

Database is 10g rel 2 on Windows.

This problem is puzzling me greatly and any help would be greatfully received.

The query below addresses about 3 million rows in a table called spot_load1.

The issue concerns a column col10 which is a character field. It holds a 6 digit value which should be a date in DDMMRR format.

It is in processing this field where the error occurs.

select col10

FROM SPOT_LOAD1

WHERE load_sequence = 17395

AND fmt6_file_number = 1

and col10 is not null

and to_date(col10,'DDMMRR') between '01-dec-06' and '28-feb-07'

fails with ora-1840 input value not long enough for data format

select count(*)

FROM SPOT_LOAD1

WHERE load_sequence = 17395

AND fmt6_file_number = 1

and col10 is not null

and length(col10) = 6

and to_date(col10,'DDMMRR') between '01-dec-06' and '28-feb-07'

runs with no errors, we are restricting to columns with a col10 width of 6. So if we go looking for columns of less or mare than 6 chars:

select count(*)

FROM SPOT_LOAD1

WHERE load_sequence = 17395

AND fmt6_file_number = 1

and col10 is not null

and length(col10) <> 6

returns zero rows.

using DDMMYY for the date format makes no difference.

The following three queries return the same number of rows:

select count(*)

FROM SPOT_LOAD1

WHERE load_sequence = 17395

AND fmt6_file_number = 1

and col10 is not null

and length(col10) = 6

and to_date(col10,'DDMMRR') between '01-dec-06' and '28-feb-07'

select count(*)

FROM SPOT_LOAD1

WHERE load_sequence = 17395

AND fmt6_file_number = 1

and col10 is not null

and length(col10) = 6

--and to_date(col10,'DDMMRR') between '01-dec-06' and '28-feb-07'

select count(*)

FROM SPOT_LOAD1

WHERE load_sequence = 17395

AND fmt6_file_number = 1

and col10 is not null

--and length(col10) = 6

--and to_date(col10,'DDMMRR') between '01-dec-06' and '28-feb-07'

If I select distinct col10 I get 90 entries, and inspecting them manually shows no noticeable issues. All have width of 6.

I am confused how to resolve the problem. I need the original query to run without error, on this data which seems to be correct.

TIA

regards

Tony