CODEWITHSUNDEEP
sqloracle-database
HeadOverFeet
HeadOverFeet

Reputation: 788

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

I want to change a format of the dates to format HH:MI:SS PM/AM in SQL Oracle.

I want to use it in case when like this:

case when to_char(a.dtime,'HH:MI:SS') >= 12:00:00 then to_char(a.dtime,'HH:MI:SS PM') else null end as date

but SQL does not want to show me all to_char times > 12:00 but it shows only those where there is 12:%%:%%.

And it does not work with to_date. Any suggestions? Thanks a lot in advance.

Upvotes: 1

Views: 10418

Answers (3)

Egor Skriptunoff
Egor Skriptunoff

Reputation: 23747

case 
  when a.dtime_appl_creation-trunc(a.dtime_appl_creation) > 0.5 then 'after noon'
  else 'before noon' 
end

Upvotes: 1

MT0
MT0

Reputation: 167991

You can use TO_CHAR( datetime, format ) to format a DATE column. The format options are given here.

This would give you the time part of a date value (12-hour clock plus AM/PM):

SELECT TO_CHAR( column_name,'HH:MI:SS AM') 
FROM   your_table

Edit - Addressing the update

You can do:

SELECT CASE WHEN TO_NUMBER( TO_CHAR( a.dtime_appl_creation, 'HH24' ) ) >= 12
            THEN TO_CHAR( a.dtime, 'HH:MI:SS PM' )
            END AS "date"
FROM   table_name

Upvotes: 5

davegreen100
davegreen100

Reputation: 2115

SELECT to_char(sysdate,'dd/MM/yyyy hh:mi:ss am') 
FROM dual

Upvotes: 3

Related Questions