Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Oracle: Avoiding NULL value in to_date

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.

like image 772
HelloWorld Avatar asked Mar 11 '14 21:03

HelloWorld


People also ask

Can NVL be used for DATE?

So, we can use the NVL function. We first need to work out what value we want to display. This could be a static date (i.e., 31-DEC-9999), for example. As you can see, the NVL function can be used to translate a NULL date value to something else.

IS NOT NULL vs <> in Oracle?

<> '' means is not an empty string, so the record does contain data (that there is an empty string) and is not actually null. So a query with is not null will return records with a value of string. empty, and a query with <> '' will return values that are neither null nor empty.

What is the difference between TO_DATE and To_char in Oracle?

To_char formats a DATE into a string using the given format mask. To_date converts a STRING into a date using the format mask.

Does sum ignore NULL values Oracle?

Oracle Documentation States "All aggregate functions except COUNT(*), GROUPING, and GROUPING_ID ignore nulls". So, if the SUB-QUERY, is returning NULL records, then SUM shall return you NULL as result.


2 Answers

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.

like image 80
Justin Cave Avatar answered Sep 23 '22 19:09

Justin Cave


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')
like image 36
Clint Avatar answered Sep 24 '22 19:09

Clint