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
LVL 1
bozworthyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

BajwaCommented:
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
/
0
bozworthyAuthor Commented:
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?
0
BajwaCommented:
No! I might be wrong here!! let other experts correct me!! But., I think you need to round off the extra time information.
0
johnsoneSenior Oracle DBACommented:
Your problem is the format string and the date.  They do not match.  There has been tightening of the rules around these things for many versions now.

If you look, your string has a : between the seconds and sub seconds, but your format has a . between the seconds and the sub seconds.

Either change '2012/03/15 11:59:59:999999 PM' to '2012/03/15 11:59:59.999999 PM'

or

change 'YYYY/MM/DD HH:MI:SS.FF AM' to 'YYYY/MM/DD HH:MI:SS:FF AM'

The query you posted should not have worked in 9i.  Not sure why it did.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bozworthyAuthor Commented:
Thanks for pointing that out.  I'll make changes in the site to fix that format string.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.