oracle - How to handle to_date exceptions in a SELECT statment to ignore those rows? -

i have following query attempting use command in crystal report working on.

select * mytable to_date(mytable.sdate, 'mm/dd/yyyy') <= {?enddate} 

this works fine, concern date may not in correct format (due user error). know when to_date function fails throws exception.. possible handle exception in such way ignores corresponding row in select statement? because otherwise report break if 1 date in entire database incorrectly formatted.

i looked see if oracle offers isdate function, seems supposed handle exception. appreciated. thanks!!

echoing tony's comment, you'd far better off storing dates in date columns rather forcing front-end query tool find , handle these exceptions.

if you're stuck incorrect data model, however, simplest option create function conversion , handles error,

create or replace function my_to_date( p_date_str in varchar2,                               p_format_mask in varchar2 )   return date   l_date date; begin   l_date := to_date( p_date_str, p_format_mask );   return l_date; exception   when others     return null; end my_to_date; 

your query become

select *    mytable  my_to_date(mytable.sdate, 'mm/dd/yyyy') <= {?enddate} 

of course, you'd want function-based index on my_to_date call in order make query reasonably efficient.