Oracle query with to_date() gets ORA-01858

user_15965781 0
Viewed 153

I am using the below syntax to convert a character field into a date within 'Toad for Oracle':

to_date(value_char, 'MM/DD/YYYY HH:MI:SS AM','NLS_DATE_LANGUAGE = American')

I receive the error:

ORA-01858: a non-numeric character was found where a numeric was expected'

I believe the error may be to single digit months and days. For example here are a couple values of the field:

3/3/2020 9:56:00 AM
12/14/2020 10:39:00 AM

Is there a specific syntax to specific no preceding '0'.

datetime oracle sql
3 Answers

You don't give a database version, but the following should tell you where the problem is if your are in 12.2 or later.

select value_char
from table
where to_date(value_char default null on conversion error,
          'MM/DD/YYYY HH:MI:SS AM','NLS_DATE_LANGUAGE = American') is null;
user_257140

Originally, I had used a where statement to filter for the needed category. I have also tried a subquery (in the from clause) with the category specified but still receiving same error.

Oracle won't necessarily apply all filters before attempting data conversions; particularly if you are using that converted date value within a filter/join condition.

Fortunately case expressions use short-circuit evaluation, so you can use one to restrict when the conversion is attempted:

case when value_type = 'DATE' then
  to_date(value_char, 'MM/DD/YYYY HH:MI:SS AM')
end

... where value_type = 'DATE' is whatever category check you would use to identify a row that holds a suitable value - and then you still have to hope you don't have malformed data, of course, but that's the cost of storing everything as strings.

If you're on 12cR2+ then @Gary's suggestion to use the to_date() function on conversion error clause with would also work - on it's own, or combined with this so it's only catching malformed values.

user_2663040

In the Given-case you cannot use one-simple-query which fits-all directly.

  • you have to isolate-rows based on the resulting rows obtained from below query and perform update on those sets of rows to make them eligible dates either using substr, lpad or combination of both.
  • building a complex "case" in single SQL query also

Note i've assumed column-name and table-name; Hope you get the Drift.

select length(value_char),count(1) 
    from your_table
        group by length(value_char);
user_55457740
Related