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

Oracle Database

Last Comment

bozworthy

8/22/2022 - Mon

Bajwa

3/15/2012

It means that the data does not have the full information to decipher whether it is AM or PM. You might need to convert the data to make it to timestamp format or truncate the data so that extra (mili seconds) are rounded off to seconds.

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
/

bozworthy

3/15/2012

ASKER

Looking at the sample query file I posted (which has been working in 9i for several years), am I correct that you're saying the SQL must be modified in some way for 10g to figure out what AM/PM is? Why would 9i know but not 10g?

Bajwa

3/15/2012

No! I might be wrong here!! let other experts correct me!! But., I think you need to round off the extra time information.

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst

William Peck

ASKER CERTIFIED SOLUTION

johnsone

3/15/2012

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options