5

I have a problem with Oracle 11g specific timestamp format.

This is what I have: select to_timestamp('21-OCT-15 08.24.30.000000000 PM','DD-MON-RR HH.MI.SSXFF AM') from dual;

Response from database: ORA-01855: AM/A.M. or PM/P.M. required 01855. 00000 - "AM/A.M. or PM/P.M. required"

I have also tried to alter session settings with several commands and still nothing.

alter session set NLS_LANGUAGE='ENGLISH';
alter session set NLS_DATE_LANGUAGE='ENGLISH';
alter session set NLS_TIMESTAMP_FORMAT = 'DD-MON-RR HH.MI.SSXFF AM';
alter session set NLS_TIMESTAMP_TZ_FORMAT='DD-MON-RR HH.MI.SSXFF AM';

I can't change timestamp format in SELECT statement, need to stay as it is. I guess the issue is in session settings.

Someone experienced in oracle database administration can suggest something, I will try. I know there are a couple of similar posts but I didn't find a solution. Thanks

Here are my session settings. 

select * from nls_session_parameters;

Session Parameters

peter.hrasko.sk
  • 4,043
  • 2
  • 19
  • 34
Josip
  • 91
  • 1
  • 1
  • 7
  • Maybe the problem is that you use a period `.` as separator for time components and as (default) radix character. Does it work with `'DD-MON-RR HH.MI.SSXFF9 AM'`? `NLS_LANGUAGE` sets the language of error messages, I assume you are mean `NLS_DATE_LANGUAGE`. – Wernfried Domscheit Nov 21 '15 at 12:11
  • @WernfriedDomscheit I tried change NLS_DATE_LANGUAGE also and still the same issue. Also with your suggestion changing NLS_TIMESTAMP_FORMAT doesn't work. – Josip Nov 21 '15 at 12:29
  • can you run `select * from nls_session_parameters;` and paste the formatted result in the question? It is working fine for me in 12c. Once you give the details, we will compare the difference. – Utsav Nov 21 '15 at 12:34
  • @Utsav I added in original post picture with results. – Josip Nov 21 '15 at 12:45

4 Answers4

7

The core problem is that, on the session level, you have nls_numeric_characters=',.' while your timestamp string contains dot (.) as the seconds-from-microseconds delimiter instead.

The to_timestamp() function can accept a third parameter for overrides of the NLS settings. Here's a small demo for you ...

Connected to Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 
Connected as ******@******

--- This is how it behaves in your database (with "," as the decimals separator) ...

SQL> alter session set nls_numeric_characters = ',.';

Session altered

SQL> select to_timestamp('21-OCT-15 08.24.30.000000000 PM','DD-MON-RR HH.MI.SSXFF AM') as xx from dual;

select to_timestamp('21-OCT-15 08.24.30.000000000 PM','DD-MON-RR HH.MI.SSXFF AM') as xx from dual

ORA-01855: AM/A.M. or PM/P.M. required

--- This is how it behaves in my database (with "." as the decimals separator) ...

SQL> alter session set nls_numeric_characters = '. ';

Session altered

SQL> select to_timestamp('21-OCT-15 08.24.30.000000000 PM','DD-MON-RR HH.MI.SSXFF AM') as xx from dual;

XX
-------------------------------------------------
21.10.15 20:24:30.000000000

--- Now back to your database settings and let's make the conversion NLS-settings-indepenent ...

SQL> alter session set nls_numeric_characters = ',.';

Session altered

SQL> select to_timestamp('21-OCT-15 08.24.30.000000000 PM','DD-MON-RR HH.MI.SSXFF AM', 'nls_numeric_characters = ''. ''') as xx from dual;

XX
-------------------------------------------------
21.10.15 20:24:30,000000000

SQL>

Please notice the third parameter to the to_timestamp() function in the third SELECT. That's what you could do, too, apart from all the other correct answers.

peter.hrasko.sk
  • 4,043
  • 2
  • 19
  • 34
1

Take a backup of your NLS settings and run these.

    ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-RRRR HH24:MI:SS';
    ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';
    ALTER SESSION SET NLS_TIME_FORMAT='HH.MI.SSXFF AM';
    ALTER SESSION SET NLS_TIMESTAMP_FORMAT='DD-MON-RR HH.MI.SSXFF AM';
    ALTER SESSION SET NLS_TIME_TZ_FORMAT='HH.MI.SSXFF AM TZR';
    ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='DD-MON-RRRR HH.MI.SSXFF AM TZR';

Then run the SQL statement again.

Utsav
  • 7,914
  • 2
  • 17
  • 38
1

Finally I have added Territory on the previous list altered session properties to make it work for me. Thanks

ALTER SESSION SET NLS_TERRITORY=AMERICA;
ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';
ALTER SESSION SET NLS_TIME_FORMAT='HH.MI.SSXFF AM';
ALTER SESSION SET NLS_TIMESTAMP_FORMAT='DD-MON-RR HH.MI.SSXFF AM';
ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='DD-MON-RRRR HH.MI.SSXFF AM TZR';
ALTER SESSION SET NLS_TIME_TZ_FORMAT='HH.MI.SSXFF AM TZR';
Josip
  • 91
  • 1
  • 1
  • 7
-1

Try this: SELECT TO_CHAR(TO_TIMESTAMP(**'2015-11-21-13:03:07.04776'**),'DD-MON-RR HH.MI.SSXFF AM') FROM DUAL;The timestamp format between quotes should be the same as the result of this query: SELECT TO_CHAR(LOCALTIMESTAMP) FROM DUAL

Nik
  • 9
  • 1