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

or

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

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.

Please Note- to_char(tran_datetime,'HH24') is a very obvious option, but I am looking to use EXTRACT() function specifically.

3

3 Answers

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

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

Although you don't need the to_char at all:

SQL> select extract(hour from cast(sysdate as timestamp)) from dual;
EXTRACT(HOURFROMCAST(SYSDATEASTIMESTAMP))
----------------------------------------- 15

2

select to_char(tran_datetime,'HH24') from test;
TO_CHAR(tran_datetime,'HH24')
------------------
16 

1

simple and easier solution:

select extract(hour from systimestamp) from dual;
EXTRACT(HOURFROMSYSTIMESTAMP)
----------------------------- 16 

Your Answer

Sign up or log in

Sign up using Google Sign up using Facebook Sign up using Email and Password Submit

Post as a guest

Post Your Answer Discard

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy