ORA-01855: AM/A.M. or PM/P.M. required

Question

I get the error: ORA-01855: AM/A.M. or PM/P.M. required

when I try to execute following query.

  INSERT INTO TBL(ID,START_DATE) 
    values (123, TO_DATE ('3/13/2012 9:22:00 AM', 'MM/DD/YYYY HH:MI AM'))

Where my START_DATE column is of type "Date".

I have executed following query and it gave no errors, still not success yet in above issue:

ALTER SESSION SET NLS_DATE_FORMAT = "MM/DD/YYYY HH:MI AM";

Answer 1

Your format mask must match the format of the string you are converting. So you would either want to add SS to the format mask or remove the seconds from the string

INSERT INTO TBL(ID,START_DATE) 
  values (123, TO_DATE ('3/13/2012 9:22:00 AM', 'MM/DD/YYYY HH:MI:SS AM'))

or

INSERT INTO TBL(ID,START_DATE) 
  values (123, TO_DATE ('3/13/2012 9:22 AM', 'MM/DD/YYYY HH:MI:SS AM'))

If you want to accept a string that contains seconds but you don't want to store the seconds in the database (in which case Oracle will always store 0 for the seconds), you can use the TRUNC function

INSERT INTO TBL(ID,START_DATE) 
  values (123, TRUNC( TO_DATE ('3/13/2012 9:22:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'MI') )