Oracle: Avoiding NULL value in to_date

Question

I have a functional select statement that has a where clause, in the where clause there is a statement like so...

to_date(camp.start_date, 'MM/DD/YYYY') >= to_date(:from_date, 'YYYY-MM-DD HH24:MI:SS')

However, if camp.start_date is NULL or has no rows then it is throwing an exception -

ORA-01858: a non-numeric character was found where a numeric was expected

camp.start_date is actually a VARCHAR2 that I need to convert into a date, (yes I know it probably should be a date field but I don't have the options to change this).

I tried something like this...

to_date(NVL(camp.start_date,SYSDATE), 'MM/DD/YYYY') >= 
to_date(:from_date, 'YYYY-MM-DD HH24:MI:SS')

Which still is giving me an error. Also tried

where camp.start_date is not null and to_date(camp.start_date, 'MM/DD/YYYY') >= to_date(:from_date, 'YYYY-MM-DD HH24:MI:SS')

same issue. What is the best way around this? Basically to_date is exploding and throwing an error when camp.start_date is not a valid date.

Also for some reason this is ok in db_visualizer but it throws an error using Rails? — HelloWorld

Justin Cave Justin Cave · Accepted Answer · 2014-03-11T21:38:34

If start_date is NULL, no exception is thrown.

select to_date( null, 'mm/dd/yyyy' ) 
  from dual

is a perfectly valid SQL statement that returns NULL.

The error you are getting strongly implies that at least some of the rows in the start_date column are not actually strings in the format you expect or that map to invalid dates (i.e. the string '13/35/2007'). You can write a function that tests to see whether a string can be converted to a date and return either the converted date or a NULL. You can then use that instead of to_date.

CREATE OR REPLACE FUNCTION my_to_date( p_str    IN VARCHAR2,
                                       p_format IN VARCHAR2 )
  RETURN DATE
IS
BEGIN
  RETURN to_date( p_str, p_format );
EXCEPTION
  WHEN OTHERS
  THEN
    RETURN NULL;
END;

and then use my_to_date instead of to_date. That should eliminate the error you're getting. You'll probably want to clean up the data, though, to get rid of the invalid strings.

Oracle: Avoiding NULL value in to_date

4 Answers