SQL & PL/SQL

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