Geek Answers Handbook

Oracle inserts timestamp - AM / AM or PM / PM required

In SQL Developer, I created an .sql export file (really big). It contains hundreds of inserts such as:

"Insert into SCHEMA.TABLE (
 ... ,
 CREATEDATE,
 MODIFIEDDATE,
 ....
) 
values (
 ... , 
 to_timestamp('20-AUG-12 01.09.53.271000000 AM','DD-MON-RR HH.MI.SS.FF AM'),
 to_timestamp('20-AUG-12 01.09.53.271000000 PM','DD-MON-RR HH.MI.SS.FF PM'),
 ...
);"

When I run it in SQL Developer, it works fine. But I can not import the .sql file with SQL Developer (or I do not know how to do this).

When I run it using SQL * Plus, I get the following error:

ORA-01855: AM / AM or PM / PM required

When I remove the AM / PM keywords from the insert, SQL * Plus can insert the row, with them, no luck. What is the problem with AM / PM?

Here is the exact insert command:

Insert into SCHEMA.TABLE(

ENTRYID,GROUPID,COMPANYID,USERID,USERNAME,CREATEDATE,MODIFIEDDATE,
CLASSNAMEID,CLASSPK,CLASSUUID,VISIBLE,STARTDATE,ENDDATE,PUBLISHDATE,
EXPIRATIONDATE,MIMETYPE,TITLE,DESCRIPTION,SUMMARY,URL,HEIGHT,WIDTH,
PRIORITY,VIEWCOUNT

) values (

11902,11005,10136,10178,'Test Test',
to_timestamp('20-AUG-12 01.09.53.271000000 AM','DD-MON-RR HH.MI.SS.FF AM'),
to_timestamp('20-AUG-12 01.09.53.271000000 PM','DD-MON-RR HH.MI.SS.FF PM'),
10076,11900,'AAA',1,null,null,null,null,null,
'ABC','XyZ',null,null,0,0,0,2563

);
+4
source share
3 answers

- NLS; NLS_LANGUAGE, . , :

SQL> alter session set NLS_DATE_LANGUAGE='ENGLISH';

Session altered.

SQL> select value from nls_session_parameters
  2  where parameter = 'NLS_DATE_LANGUAGE';

VALUE
--------------------------------------------------------------------------------
ENGLISH

SQL> insert into t42 (createddate)
  2  values (to_timestamp('20-08-12 01.09.53.271000000 AM','DD-MM-RR HH.MI.SS.FF PM'));

1 row created.

NLS_DATE_LANGUAGE :

alter session set NLS_DATE_LANGUAGE='SLOVAK';

Session altered.

SQL> insert into t42 (createddate)
  2  values (to_timestamp('20-AUG-12 01.09.53.271000000 AM','DD-MON-RR HH.MI.SS.FF PM'));
values (to_timestamp('20-AUG-12 01.09.53.271000000 AM','DD-MON-RR HH.MI.SS.FF PM'))
                     *
ERROR at line 2:
ORA-01855: AM/A.M. or PM/P.M. required

NLS_LANGUAGE, -, , , -:

SQL> alter session set NLS_LANGUAGE = 'SLOVAK';

Relácia zmenená.

SQL> insert into t42 (createddate)
  2  values (to_timestamp('20-AUG-12 01.09.53.271000000 AM','DD-MON-RR HH.MI.SS.FF PM'));
values (to_timestamp('20-AUG-12 01.09.53.271000000 AM','DD-MON-RR HH.MI.SS.FF PM'))
                     *
ERROR v riadku 2:
ORA-01855: vy¿aduje sa AM/A.M. alebo PM/P.M.

, AUG , AM/PM; , , , , , . . , , NLS_LANGUAGE , AM/PM, to_char(sysdate, 'HH AM') , . NLS_DATE_LANGUAGE , (, , , , ), AM/PM.

, , NLS_DATE_LANGUAGE , script; SQL Developer, ( - , ) , , , DOPOLUDNIE/POPOLUDNIE, , ; SQL Developer NLS_TIMESTAMP_FORMAT 24- ( 4- ) , . script SQL Developer, .

+7

; , , . TO_TIMESTAMP() , NLS_DATE_LANGUAGE .

create table a ( tstamp timestamp );

Table created.

insert into a
values (to_timestamp( '20-08-12 01.09.53.271000000 AM'
                    , 'DD-MM-RR HH.MI.SS.FF PM'
                    , 'nls_date_language = ENGLISH'));

1 row created.

select *
  from a;

TSTAMP
------------------------------------------------------
20-AUG-12 01.09.53.271000

.

+1

Without any data to browse, I cannot be sure, but it sounds as if your "large SQL file" does not always (or sometimes) have timestamps in the expected format and does not use AMor PM.

Try using a 24-hour clock rather than AMor PMand see if this matches the data in the "large SQL file":

 ...
 to_timestamp('20-AUG-12 01.09.53.271000000','DD-MON-RR HH24.MI.SS.FF'),
 to_timestamp('20-AUG-12 13.09.53.271000000','DD-MON-RR HH24.MI.SS.FF'),
 ...
0
source

More articles:


All Articles