Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Atin.

Asked: July 18, 2016 - 9:22 am UTC

Last updated: July 18, 2016 - 10:29 am UTC

Version: Oracle 11 G

Viewed 1000+ times

You Asked

Hi Team,

Please let me know how we can convert 14-JUL-2016 17:11:05 PM into 17:11:05 or 05:11:05 PM only in Oracle.

Regards,
Atin

and Chris said...

First up, 17:11:05 PM is an invalid format in Oracle:

with dts as (
  select to_date('14-JUL-2016 17:11:05 PM', 'dd-mon-yyyy hh24:mi:ss pm') dt from dual
)
  select * from dts;

SQL Error: ORA-01818: 'HH24' precludes use of meridian indicator


So you'll have to decide which to use. Trimming AM/PM off the end is easier than converting the time.

Once you've done this, you can get 24 hour format with the "hh24" mask. And AM/PM with the "hh ... pm" mask:

with dts as (
  select to_date('14-JUL-2016 17:11:05', 'dd-mon-yyyy hh24:mi:ss') dt from dual
)
  select to_char(dt, 'hh24:mi:ss') "24", to_char(dt, 'hh:mi:ss PM') pm from dts;

24        PM           
17:11:05  05:11:05 PM 


Is this answer out of date? If it is, please let us know via a Comment