CODEWITHSUNDEEP
oracle-databaseoracle-sqldeveloper
Rakesh
Rakesh

Reputation: 313

Oracle Data Sort By Date and Time

I am using Oracle database. Below is Equipment working Time Sheet details which is always start from morning 7:00 AM and end on next day 7:00 AM (total 24 hours) . I need report in same way with date wise ascending and Time From 7:00 AM to next day 7:00 AM. Please advise on same.

WorkDate is Date type and rest two column are varchar2 type

CREATE TABLE TT
(
 WorkDate DATE,
 FromTime VARCHAR2(100 BYTE),
 ToTime   VARCHAR2(100 BYTE)

)

Work date   From    To
8/8/2017    7:00    7:25
8/8/2017    7:25    12:35
8/8/2017    12:35   21:55
8/8/2017    21:55   1:30
8/8/2017    1:30    7:00
8/9/2017    7:00    7:25
8/9/2017    7:25    12:35
8/9/2017    12:35   21:55
8/9/2017    21:55   1:30
8/9/2017    1:30    7:00

Upvotes: 0

Views: 134

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 95072

Well the only problem is that 7:25 comes after 12:35 in the alphabet. You need 07:25 instead. Use LPAD for this:

select *
from mytable
order by workdate, lpad(fromtime, 5, '0');

Upvotes: 2

APC
APC

Reputation: 146309

Concatenate the date with the from time, then cast to a date to sort the result set.

select * from tt
order by to_date(to_char(work_date, 'mm/dd/yyyy') || ' ' || from_date, 'mm/dd/yyyy hh24:mi')
/

The easiest thing would have been to store the FROM and TO as datetimes but no doubt there are good reasons why your application needs to do things the hard way.

Upvotes: 1

Related Questions