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;
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With