Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> need help creating procedure to query date/time range

need help creating procedure to query date/time range

From: Andrew <acgoodall_at_ev1.net>
Date: 2 Oct 2003 11:58:49 -0700
Message-ID: <f8344f71.0310021058.33559951@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 - 13:58:49 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US