Re: Comparing Dates

Brit.Willoughby_at_WichitaKS.ATTGIS.Com (Brit Willoughby) wrote-écrivait:

>Why does the following occur?
 

>SQL> r
> 1 select * from brit_attr_value where TO_DATE(attr_value) between
> 2* sysdate and sysdate+7;
>ERROR:
>ORA-01858: a non-numeric character was found where a numeric was expected
 

> attr_value is a non-null varchar2(25) in the form DD-MON-YY, so it
>should be easily converted by the TO_DATE function. This works fine in ProC,
>but fails when run directly from SQL.
 

> Any ideas?
 

> Brit Willoughby
> Brit.Willoughby_at_WichitaKS.ATTGIS.Com

You're setting in sql*plus may differ from the one used in your pro*c. Anyway, for portability and other problems, you should always specified the format of the input field (attr_value) in a to_date instruction. In pro*c your field must be truncated and in sql*plus not.

Use to_date(field,'DD-MON-YY')

Michel Lapierre Received on Mon Aug 14 1995 - 00:00:00 CEST