ERROR: Oracle ORA-01855: AM/A.M. or PM/P.M. error upgrading to Oracle 10g
We need to upgrade our Oracle database from 9i to 10g. We've performed the upgrade on the test box and are in the process of checking it for issues.
One thing that's popped up so far is the error: "Oracle ORA-01855: AM/A.M. or PM/P.M." for queries with the to_timestamp function.
I've checked the NLS settings and they appear to be the same between our 9i and 10g configurations except for a small difference in the parm = NLS_NUMERIC_CHARACTERS. I'm hoping to find a settings change to fix this rather than updating a large number of SQL statements. Attached is a sample query and the NLS settings for both. NLS-parameters-comparison.xls Sample-SQL.txt
select to_timestamp('2012/02/14 12:00:00:000000 AM','YYYY/MM/DD HH:MI:SS.FF AM') from dual
select to_timestamp('2012/02/14 12:00:00','YYYY/MM/DD HH:MI:SS') from dual