oracle EXTRACT() 24 小时格式的小时
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12916611/
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
EXTRACT() Hour in 24 Hour format
提问by AnBisw
I have something like below-
我有类似下面的东西-
EXTRACT(HOUR from CAST(to_char(tran_datetime,'DD-MON-YYYY HH24:MI:SS') AS TIMESTAMP))
tran_datetimeis DATEtype. This gives error for some rows saying HOUR must be between 1 and 12, so I understand that it cannot handle Hour in a 24 Hourformat (or military time). The below works (obviously)-
tran_datetime是DATE类型。这会为某些行提供错误HOUR must be between 1 and 12，因此我知道它无法以24 Hour格式（或军用时间）处理小时。下面的作品（显然） -
EXTRACT(HOUR from CAST(to_char(tran_datetime,'DD-MON-YYYY HH:MI:SS') AS TIMESTAMP))
or
或者
EXTRACT(HOUR from CAST(tran_datetime AS TIMESTAMP)) --12 Hr format by default
Is there a way to use EXTRACT()to get the HOURin 24 Hour format i.e. 15 for 3 PM, 13 for 1 PM etc.
有没有办法使用的方式EXTRACT()来获得HOUR以24小时格式，即15下午3点，13点1等。
Please Note- to_char(tran_datetime,'HH24')is a very obvious option, but I am looking to use EXTRACT()function specifically.
请注意 -to_char(tran_datetime,'HH24')是一个非常明显的选项，但我希望EXTRACT()专门使用函数。
回答by Alex Poole
The problem is not with extract, which can certainly handle 'military time'. It looks like you have a default timestamp format which has HHinstead of HH24; or at least that's the only way I can see to recreate this:
问题不在于extract，它当然可以处理“军事时间”。看起来您有一个默认的时间戳格式，HH而不是HH24; 或者至少这是我能看到的重新创建它的唯一方法：
SQL> select value from nls_session_parameters
2 where parameter = 'NLS_TIMESTAMP_FORMAT';
VALUE
--------------------------------------------------------------------------------
DD-MON-RR HH24.MI.SSXFF
SQL> select extract(hour from cast(to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS')
2 as timestamp)) from dual;
EXTRACT(HOURFROMCAST(TO_CHAR(SYSDATE,'DD-MON-YYYYHH24:MI:SS')ASTIMESTAMP))
--------------------------------------------------------------------------
15
alter session set nls_timestamp_format = 'DD-MON-YYYY HH:MI:SS';
Session altered.
SQL> select extract(hour from cast(to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS')
2 as timestamp)) from dual;
select extract(hour from cast(to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS') as timestamp)) from dual
*
ERROR at line 1:
ORA-01849: hour must be between 1 and 12
So the simple 'fix' is to set the format to something that does recognise 24-hours:
所以简单的“修复”是将格式设置为可以识别 24 小时的格式：
SQL> alter session set nls_timestamp_format = 'DD-MON-YYYY HH24:MI:SS';
Session altered.
SQL> select extract(hour from cast(to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS')
2 as timestamp)) from dual;
EXTRACT(HOURFROMCAST(TO_CHAR(SYSDATE,'DD-MON-YYYYHH24:MI:SS')ASTIMESTAMP))
--------------------------------------------------------------------------
15
Although you don't need the to_charat all:
虽然你根本不需要to_char：
SQL> select extract(hour from cast(sysdate as timestamp)) from dual;
EXTRACT(HOURFROMCAST(SYSDATEASTIMESTAMP))
-----------------------------------------
15
回答by Rob van Laarhoven
select to_char(tran_datetime,'HH24') from test;
TO_CHAR(tran_datetime,'HH24')
------------------
16
