[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

ERROR:  Oracle ORA-01855: AM/A.M. or PM/P.M. error upgrading to Oracle 10g

Posted on 2012-03-15
5
Medium Priority
?
2,804 Views
Last Modified: 2012-03-15
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
0
Comment
Question by:bozworthy
  • 2
  • 2
5 Comments
 
LVL 5

Expert Comment

by:Bajwa
ID: 37726590
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
 
LVL 1

Author Comment

by:bozworthy
ID: 37726624
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
 
LVL 5

Expert Comment

by:Bajwa
ID: 37726644
No! I might be wrong here!! let other experts correct me!! But., I think you need to round off the extra time information.
0
 
LVL 35

Accepted Solution

by:
johnsone earned 2000 total points
ID: 37726658
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
 
LVL 1

Author Comment

by:bozworthy
ID: 37727749
Thanks for pointing that out.  I'll make changes in the site to fix that format string.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to recover a database from a user managed backup
Suggested Courses
Course of the Month18 days, 16 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question