• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1107
  • Last Modified:

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
0
anumoses
Asked:
anumoses
  • 2
2 Solutions
 
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

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now