Home » RDBMS Server » Server Utilities » ORA-01855: AM/A.M. or PM/P.M. (oracle 11g,Linux)
ORA-01855: AM/A.M. or PM/P.M. [message #461080] Wed, 16 June 2010 08:42 Go to next message
sundarfaq
Messages: 235
Registered: October 2007
Location: Chennai
Senior Member
Hi,
During the loading data into target table using sql loader i am getting ORA-01855: AM/A.M. or PM/P.M. error.

In dat file, date format looks like Jan 1 1900 12:00:00:000AM. but i want to save in database like 1900-12-1 12:00:00:000AM.

The sql loader control file looks like

load data
infile 'emp.dat'
"str '<EORD>'"
into table emp_test
fields terminated by '<EOFD>'
trailing nullcols
(
emp_date "to_char(TO_TIMESTAMP(:emp_date, 'Mon dd yyyy hh:mi:ss:ff3 AM'),'yyyy-mon-dd hh:mi:ss:ff3')",dep,name
).

but i am able to get output in sqlplus based on the above date format.

select to_char(TO_TIMESTAMP('Jan 1 1900 1:00:00:000 ', 'Mon dd yyyy hh:mi:ss:ff3 AM'),'yyyy-mon-dd hh:mi:ss:ff3 AM') from dual


Could you please give solution?

Thanks,
Michael
Re: ORA-01855: AM/A.M. or PM/P.M. [message #461081 is a reply to message #461080] Wed, 16 June 2010 08:47 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Why don't you just store it in a timestamp?
Re: ORA-01855: AM/A.M. or PM/P.M. [message #461139 is a reply to message #461080] Wed, 16 June 2010 13:14 Go to previous message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
Why does Jan 1 1900 12:00:00:000AM translate to 1900-12-1 12:00:00:000AM?
Previous Topic: impdp error in Oracle 10g
Next Topic: Invalid geometries after import sqlldr
Goto Forum:
  


Current Time: Thu Mar 28 19:27:06 CDT 2024