Oracle: null exception in to_date

Question

I have a functional choice operator that has a where clause, in the where clause there is an instruction like this ...

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, it throws an exception -

ORA-01858: A non-numeric character was found where the number is expected,

camp.start_date is actually VARCHAR2, which I need to convert to a date (yes, I know, it probably should be a date field, but I have no way 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 gives me an error. Also tried

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

. ? to_date , camp.start_date .

Answer 1

start_date NULL, .

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

- SQL, NULL.

, start_date (, "13/35/2007" ), , , , NULL. 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;

my_to_date to_date. , . , , , .

Answer 2

sysdate char:

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

:

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

, .

Answer 4

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

is there from_dateif it from_datehas a nonzero date type value? If it from_datecan be null or not a date value, then do it?

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

Something like comparing apples to apples?