EXTRACT Hour in 24 Hour format

Question

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

User · Answer

select to char tran datetime  HH24   from test   TO CHAR tran datetime  HH24   ------------------ 16

User · Answer

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 gt  select value from nls session parameters   2  where parameter    NLS TIMESTAMP FORMAT    VALUE -------------------------------------------------------------------------------- DD-MON-RR HH24 MI SSXFF  SQL gt  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 gt  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 gt  alter session set nls timestamp format    DD-MON-YYYY HH24 MI SS    Session altered   SQL gt  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 gt  select extract hour from cast sysdate as timestamp   from dual   EXTRACT HOURFROMCAST SYSDATEASTIMESTAMP   -----------------------------------------                                        15

User · Answer

simple and easier solution  select extract hour from systimestamp  from dual   EXTRACT HOURFROMSYSTIMESTAMP  -----------------------------                            16

[oracle] EXTRACT() Hour in 24 Hour format

Examples related to oracle