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.

Answer 1

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.

Answer 2

You need to convert sysdate to the valid char string format:

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

Answer 3

Does

NVL(TO_DATE(start_date,'MM/DD/YYYY'),SYSDATE) >= from_date

make more sence if from_date has a not null date type value? If from_date can be null or is not a date value then do this?

NVL(TO_DATE(start_date,'MM/DD/YYYY'),SYSDATE) >= NVL(TO_DATE(from_date,'MM/DD/YYYY'),SYSDATE)

Something like comparing apples to apples?

Answer 4

You can use like this:

NVL(TO_CHAR(SYSDATE,'DD-MM-YYYY'),' ')

This will convert the date to string format and then to empty string.