Ora-01821 date format not recognized

Question

I want to change the string into date field but I received the error ora- 01821 date format not recognized while executing the following query.also I need am/pm in the db .how to achieve that

update bucket set closed = to_date(closed_on,'dd-mon-yy hh.mi.ss.ff a.m')

Closed_on value will be like 29-MAY-19 09.01.16.00000000 PM

Answer 1

I think you want to use TO_TIMESTAMP - date cannot store your milliseconds, so it doesn't understand the FF

select  to_timestamp('29-MAY-19 09.01.16.00000000 PM','dd-mon-yy hh.mi.ss.FF8 AM') from dual

Note, as GMB has pointed out, you can't get oracle to parse a.m - you'll have to make it either a.m. or am. No half measures :) (hopefully its a copy pasta error)

If you're desperate to use TO_DATE, cut the milliseconds off:

select 
  to_date(
    SUBSTR('29-MAY-19 09.01.16.00000000 PM', 1, 18) || SUBSTR('29-MAY-19 09.01.16.00000000 PM', -2, 2),
    'dd-mon-yy hh.mi.ssAM'
  ) 
from dual

If your time str is a.m. make the second SUBSTR use , -4, 4) - "start from right, 4 places left, then take 4 chars"

If the millis are always 00000000 you could neaten this up with a REPLACE(timeStr, '.00000000 ', '') instead

Answer 2

Fractional seconds (FF) do not with data type DATE. So if your table's column closed_on is of type Date, it will not work. Caius Jard have suggested good alternative solutions.

Answer 3

Another option is to skip the characters using # as follows:

SQL> SELECT
  2      TO_DATE('29-MAY-19 09.01.16.00000000 PM', 'DD-MON-YYYY HH.MI.SS.######## AM')
  3  FROM
  4      DUAL;

TO_DATE('29-MAY-1909.01
-----------------------
29-MAY-0019 09.01.16 PM

SQL>

The number of # should be number of characters to be skipped.