ORA-01843 NOT A VALID MONTH ERROR

[SalShaikh]

hi all sql/oracle gurus and experts,
I need assistance with the following problem
I have a table (structure follows)
Name Null? Type
------------------------------- -------- ----
SAMPLE_ID NOT NULL NUMBER(10)
NAME VARCHAR2(40)
TEXT_VALUE VARCHAR2(255)
HIDDEN CHAR(1)

I need to convert the text_value to a date so I can use it in date range where clauses
when i do a simple select with a to_date(text_value, 'MON-DD-YYYY) it works fine
but when i try it in where clause as below I get a ORA-01843 not a valid month error... any help would be appreciated.


SELECT NAME,
TEXT_VALUE,
TO_DATE(TEXT_VALUE, 'MON DD YYYY')
FROM NAIS_SAMPLE_ATTRIBUTES
WHERE SAMPLE_ID = 200000288
AND NAME = 'COLLECTION DATE'
AND TO_DATE(TEXT_VALUE, 'MON DD YYYY') = TO_DATE('JUN 24 2003','MON DD YYYY')
/

 

[andys288]

Fantastic!

Thats done just what I needed!

Thanks Dima

 

[sem]

Is it possible to change this column type to DATE? I suppose this may prevent further errors.

Regards, Dima

 

[andys288]

The last time I tried that I completely screwed up our Sage Accounts system, so I SCARED to do it!

Thanks again for your help