oracle - SQL How do I extract time from a date to HH:MI:SS PM/AM? -

i want change format of dates format hh:mi:ss pm/am in sql oracle.

i want use in case when this:

case when to_char(a.dtime,'hh:mi:ss') >= 12:00:00 to_char(a.dtime,'hh:mi:ss pm') else null end date

but sql not want show me to_char times > 12:00 shows there 12:%%:%%.

and not work to_date. suggestions? lot in advance.

you can use to_char( datetime, format ) format date column. format options given here.

this give time part of date value (12-hour clock plus am/pm):

select to_char( column_name,'hh:mi:ss am')    your_table 

edit - addressing update

you can do:

select case when to_number( to_char( a.dtime_appl_creation, 'hh24' ) ) >= 12             to_char( a.dtime, 'hh:mi:ss pm' )             end "date"   table_name