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.
This question is related to
oracle
select to_char(tran_datetime,'HH24') from test;
TO_CHAR(tran_datetime,'HH24')
------------------
16
simple and easier solution:
select extract(hour from systimestamp) from dual;
EXTRACT(HOURFROMSYSTIMESTAMP)
-----------------------------
16
Source: Stackoverflow.com