24小时格式的EXTRACT()小时 [英] EXTRACT() Hour in 24 Hour format

查看:80
本文介绍了24小时格式的EXTRACT()小时的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有类似下面的内容

I have something like below-

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

tran_datetimeDATE类型.这会给某些行说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屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆