Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> need help creating procedure to query date/time range
here's the situation: the database I'm going after has a table which
DOES NOT have a TIMESTAMP column, why oh why!!!
Instead they decided to use separate date and time columns and make
them both a number datatype, arhhh...
e.g. table called detail
field orig_date (number) e.g. 1031001 = 10/01/03
orig_time (number) e.g. 70000 = 07:00am or 190000 = 07:00pm
So what I want is to be able to query a specific date AND time range -
this would be easy if they had used a timestamp field - but they
didn't.
Querying just the date as seen in example I created below is straight
forward.
The problem I have is getting a specific time range e.g. 09/29/03 19:00 through 10/01/03 07:00
any ideas??
TIA - Andrew.
create or replace package test_pkg as
type rc is ref cursor;
procedure qry_test(sdate in number, edate in number, res_out in out
rc);
end;
/
create or replace package body test_pkg as
procedure qry_test(sdate in number, edate in number, res_out in out rc) is
total_queuetime number; username varchar(9);
select t2.unum as username, t1.queue_time as total_queuetime, t1.orig_date, t1.orig_time from detail t1, users t2 where (t1.orig_date >= sdate AND t1.orig_date <= edate) AND t1.dest = t2.ext_num order by total_queuetime desc;
end test_pkg; Received on Thu Oct 02 2003 - 13:58:49 CDT