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.

Solution 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.

Solution 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')

Comments

[HelloWorld]

That was it, some of the dates are like so 1/1/2013 perhaps they should be 01/01/2013?

[Justin Cave]

@HelloWorld - That would convert successfully, it's not a matter of leading 0's. You can look for rows where my_to_date( camp.start_date, 'MM/DD/YYYY') is null and camp.start_date is not null to see what the invalid strings are.

[Zoe stands with Ukraine]

This does not provide an answer to the question. You can search for similar questions, or refer to the related and linked questions on the right-hand side of the page to find an answer. If you have a related but different question, ask a new question, and include a link to this one to help provide context. See: Ask questions, get answers, no distractions

[William Robertson]

A space (' '), not an empty string (''). And only on days when sysdate is null.