7

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.

HelloWorld
  • 4,042
  • 5
  • 31
  • 58

4 Answers4

11

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.

Justin Cave
  • 212,688
  • 21
  • 342
  • 361
  • 1
    That was it, some of the dates are like so 1/1/2013 perhaps they should be 01/01/2013? – HelloWorld Mar 11 '14 at 21:43
  • @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. – Justin Cave Mar 11 '14 at 21:45
1

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')
dario
  • 4,811
  • 12
  • 26
  • 31
Clint
  • 11
  • 1
1

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.

EzLo
  • 12,897
  • 10
  • 28
  • 33
venkatesh
  • 11
  • 1
-2

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?

Ralf
  • 13,322
  • 4
  • 31
  • 55
Vincent Quiles
  • 1
  • 1
    This does not provide an answer to the question. You can [search for similar questions](//stackoverflow.com/search), 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](//stackoverflow.com/questions/ask), and include a link to this one to help provide context. See: [Ask questions, get answers, no distractions](//stackoverflow.com/tour) – Zoe Jan 17 '19 at 17:24