Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Extracting records by date format, bad record somewhere

GlenStromFeb 9 2024

Oracle 11.2 on windows (soon to be upgraded to 19C)

I have an app, that for some unknown reason, in the new year started transposing dates for a date column, taking a date & time in MMDDYYHH24:MI format and entering DDMMYYHH24:MI into the column, which is then extracted from another date column in reports / queries to get elapsed time.

As you can imagine, transposing the MMDDYY to DDMMYY throws this all out of whack.

It also clears itself up after the 12th day, but that's not what this question is about.

I want to write an update query to convert the date to the correct date for records affected, so I came up with this:

UPDATE loads
SET loading_date = TO_DATE(
   EXTRACT(DAY FROM loading_date) || '-' ||
   EXTRACT(MONTH FROM loading_date) || '-' ||
   EXTRACT(YEAR FROM loading_date) || ' ' ||
   TO_CHAR(loading_date, 'HH24:MI'),
   'DD-MM-YY HH24:MI'
)
WHERE..

But first I want to query to find and test records:

SELECT
   to_char(loading_date,'dd-mon-yy hh24:mi') as entered_date,
   to_char(TO_DATE(
       TO_CHAR(loading_date, 'DD') || '-' ||
       TO_CHAR(loading_date, 'MM') || '-' ||
       TO_CHAR(loading_date, 'YY') || ' ' ||
       TO_CHAR(loading_date, 'HH24:MI'),
       'MM-DD-YY HH24:MI'
   ),'dd-mon-yy hh24:mi') AS corrected_date
FROM loads
WHERE..

This works for a single record when I put criteria in where clause, showing the entered date and corrected date.

Trouble is, when I broaden the query to get all records within date criteria, I get ORA-01858: a non-numeric character was found where a numeric was expected

All the dates in the column look fine when I just query them without the conversion part, and being a date field I don't know how incorrect data could be in there.

This is in SQL Developer, when I run in SQLPLUS I get:

I am not sure how to find the offending record, any ideas?

Thanks in advance

Comments
Post Details
Added on Feb 9 2024
4 comments
127 views