CODEWITHSUNDEEP
sqloracle-databasedatetime
Daniel
Daniel

Reputation: 21

Oracle query with to_date() gets ORA-01858

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

Upvotes: 2

Views: 330

Answers (3)

Nizam - Madurai
Nizam - Madurai

Reputation: 322

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);

Upvotes: 0

Alex Poole
Alex Poole

Reputation: 191275

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.

Upvotes: 3

Gary Myers
Gary Myers

Reputation: 35401

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;

Upvotes: 3

Related Questions