database Oracle 特定的时间戳格式 'DD-MON-RR HH.MI.SSXFF AM'
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33843100/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle specific timestamp format 'DD-MON-RR HH.MI.SSXFF AM'
提问by Josip
I have a problem with Oracle 11g specific timestamp format.
我对 Oracle 11g 特定的时间戳格式有疑问。
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;
这就是我所拥有的：
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"
来自数据库的响应： 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.
我无法在 SELECT 语句中更改时间戳格式，需要保持原样。我想问题出在会话设置中。
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
有oracle数据库管理经验的可以给点建议，我试试。我知道有几个类似的帖子，但我没有找到解决方案。谢谢
Here are my session settings.
这是我的会话设置。
select * from nls_session_parameters;
回答by nop77svk
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.
核心问题是，在会话级别，您nls_numeric_characters=',.'的时间戳字符串包含点 ( .) 作为秒从微秒分隔符。
The to_timestamp()function can accept a third parameter for overrides of the NLS settings. Here's a small demo for you ...
该to_timestamp()函数可以接受用于覆盖 NLS 设置的第三个参数。这是一个小演示给你......
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.
请注意第三to_timestamp()个 SELECT 中函数的第三个参数。除了所有其他正确答案之外，这也是您可以做的。
回答by Utsav
Take a backup of your NLS settingsand run these.
备份您的 NLS 设置并运行这些设置。
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.
然后再次运行 SQL 语句。
回答by Josip
Finally I have added Territory on the previous list altered session properties to make it work for me. Thanks
最后，我在之前的列表中添加了 Territory，更改了会话属性以使其对我有用。谢谢
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';
回答by Nik
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
试试这个：SELECT TO_CHAR(TO_TIMESTAMP(**'2015-11-21-13:03:07.04776'**),'DD-MON-RR HH.MI.SSXFF AM') FROM DUAL;引号之间的时间戳格式应该与这个查询的结果相同：SELECT TO_CHAR(LOCALTIMESTAMP) FROM DUAL
