oracle - 24小时格式的EXTRACT()小时

我有下面的东西-

EXTRACT(HOUR from CAST(to_char(tran_datetime,'DD-MON-YYYY HH24:MI:SS') AS TIMESTAMP))

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))

要么

EXTRACT(HOUR from CAST(tran_datetime AS TIMESTAMP))  --12 Hr format by default

是否可以使用EXTRACT()以24小时格式获取HOUR，即15表示3 PM，13表示1 PM等。

请注意-to_char(tran_datetime,'HH24')是一个非常明显的选项，但我希望专门使用EXTRACT()函数。

最佳答案

问题不在于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

因此，简单的“解决方案”是将格式设置为可以识别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

尽管您根本不需要to_char：

SQL> select extract(hour from cast(sysdate as timestamp)) from dual;

EXTRACT(HOURFROMCAST(SYSDATEASTIMESTAMP))
-----------------------------------------
                                       15