3

I get the error: ORA-01855: AM/A.M. or PM/P.M. required

when I try to execute following query. 

  INSERT INTO TBL(ID,START_DATE) 
    values (123, TO_DATE ('3/13/2012 9:22:00 AM', 'MM/DD/YYYY HH:MI AM'))

Where my START_DATE column is of type "Date".

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

ALTER SESSION SET NLS_DATE_FORMAT = "MM/DD/YYYY HH:MI AM";
RMN
  • 754
  • 9
  • 25
  • 45
  • 3
    When asking a question if you highlight your code and press the `{}` button at the top of your edit window you code will be formatted so that it's easier to read. In answer to your question you have some seconds in the `to_date` but they're not included in the format mask. – Ben Mar 12 '12 at 09:16
  • Ben, I dont want SS to be saved. I want to save only till minutes and then AM/PM. – RMN Mar 12 '12 at 09:18

2 Answers2

7

Your format mask must match the format of the string you are converting. So you would either want to add SS to the format mask or remove the seconds from the string

INSERT INTO TBL(ID,START_DATE) 
  values (123, TO_DATE ('3/13/2012 9:22:00 AM', 'MM/DD/YYYY HH:MI:SS AM'))

or

INSERT INTO TBL(ID,START_DATE) 
  values (123, TO_DATE ('3/13/2012 9:22 AM', 'MM/DD/YYYY HH:MI:SS AM'))

If you want to accept a string that contains seconds but you don't want to store the seconds in the database (in which case Oracle will always store 0 for the seconds), you can use the TRUNC function

INSERT INTO TBL(ID,START_DATE) 
  values (123, TRUNC( TO_DATE ('3/13/2012 9:22:00 AM', 'MM/DD/YYYY HH:MI:SS AM'), 'MI') )
Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • How do i remove it from front-end. I am getting that string as it is from front-end. Is there a way I can change/allow only that string to come. In frontend, it is datetime format. – RMN Mar 12 '12 at 09:29
  • @RMN- If you always want the number of seconds to be stored as 0, I updated my answer to include an option of truncating the `DATE` to the minute. – Justin Cave Mar 12 '12 at 09:33
-1

I got the same error when running the form builder, It was fixed by changing the Filed DataType from TIME to CHAR.

Md Faramawy
  • 17
  • 3