24小时格式的EXTRACT()小时 [英] EXTRACT() Hour in 24 Hour format
问题描述
我有类似下面的内容
I have something like below-
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
格式(或军事时间)处理小时".下面的作品(显然)-
tran_datetime
is DATE
type. 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 Hour
format (or military time). The below works (obviously)-
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等.
Is there a way to use EXTRACT()
to get the HOUR
in 24 Hour format i.e. 15 for 3 PM, 13 for 1 PM etc.
请注意-to_char(tran_datetime,'HH24')
是一个非常明显的选项,但我希望专门使用EXTRACT()
功能.
Please Note- to_char(tran_datetime,'HH24')
is a very obvious option, but I am looking to use EXTRACT()
function specifically.
推荐答案
问题不在于extract
,它肯定可以处理军事时间".看起来您具有默认的时间戳格式,其格式为HH
而不是HH24
;或至少这是我看到的重新创建它的唯一方法:
The problem is not with extract
, which can certainly handle 'military time'. It looks like you have a default timestamp format which has HH
instead of HH24
; or at least that's the only way I can see to recreate this:
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小时的格式:
So the simple 'fix' is to set the format to something that does recognise 24-hours:
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
这篇关于24小时格式的EXTRACT()小时的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!