Solved

Convert military time to regular in oracle

Posted on 2014-01-15
3
794 Views
Last Modified: 2014-01-15
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
Comment
Question by:anumoses
  • 2
3 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 total points
ID: 39782572
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
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 250 total points
ID: 39782601
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
 
LVL 6

Author Closing Comment

by:anumoses
ID: 39782641
Thanks
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle Next Available Number 2 41
Queries 15 38
Not able to add the URL for the   access control list - oracle plsql 10 96
ORA-02288: invalid OPEN mode 2 25
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question