Re: need help creating procedure to query date/time range

Andrew,

You need to write a PL/SQL function that will take 2 numbers and return a time stamp. You can use that function in the where clause of your query.

Hope this helps

"Andrew" <acgoodall_at_ev1.net> wrote in message news:f8344f71.0310021058.33559951_at_posting.google.com...
> 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);
> begin
> open res_out for
> 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 qry_test;
>
> end test_pkg;
Received on Thu Oct 02 2003 - 14:43:59 CDT