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

i error: ora-01855: am/a.m. or pm/p.m. required

when try execute following query.

  insert tbl(id,start_date)      values (123, to_date ('3/13/2012 9:22:00 am', 'mm/dd/yyyy hh:mi am'))

where start_date column of type "date".

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

alter session set nls_date_format = "mm/dd/yyyy hh:mi am";

your format mask must match format of string converting. either want add ss format mask or remove seconds string

insert tbl(id,start_date)    values (123, to_date ('3/13/2012 9:22:00 am', 'mm/dd/yyyy hh:mi:ss am'))

insert tbl(id,start_date)    values (123, to_date ('3/13/2012 9:22 am', 'mm/dd/yyyy hh:mi:ss am'))

if want accept string contains seconds don't want store seconds in database (in case oracle store 0 seconds), can use trunc function

insert tbl(id,start_date)    values (123, trunc( to_date ('3/13/2012 9:22:00 am', 'mm/dd/yyyy hh:mi:ss am'), 'mi') )

OSX