Convert military time to regular in oracle

select sch_date,sch_time,
       site_id,first_name,last_name
  from hbc_donor_schedule hds
 where SCH_DATE = trunc(sysdate)
   AND STATUS = 'CNFRM'
   AND PROC_CODE = 'WB'
   AND DRIVE_ID IS NULL
   and donation_type = 'AL'

This is how my data is stored in the table. I want to order by sch_time asc. How can I convert to regular time and then order by?

SCH_DATE,SCH_TIME,SITE_ID,FIRST_NAME,LAST_NAME

1/15/2014,17:00:00,LOCN100326,SANDRA,FERRIS

1/15/2014,17:40:00,LOCN200034,DEBRA,GUARASCIO

1/15/2014,12:00:00,LOCN100024,KEITH,HOELTERHOFF

1/15/2014,17:00:00,LOCN101661,DARCY,KAROUZOS
LVL 6
anumosesAsked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
Expected results?

Convert what you have to a date then back to a string.

Something like:
to_char(to_date(sch_time,'HH24:MI:SS'),'HH:MI:SS AM')

Order just by the date conversion:
to_date(sch_time,'HH24:MI:SS')
0
 
slightwv (䄆 Netminder) Commented:
I figured maybe I had better show the date and time just in case:

to_char(to_date(SCH_DATE || SCH_TIME,'MM/DD/YYYYHH24:MI:SS'),'MM/DD/YYYY HH:MI:SS AM')

Then the order:
to_date(SCH_DATE || SCH_TIME,'MM/DD/YYYYHH24:MI:SS')
0
 
anumosesAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.