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 -> Re: need help creating procedure to query date/time range

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

From: Andrew <acgoodall_at_ev1.net>
Date: 3 Oct 2003 13:52:06 -0700
Message-ID: <f8344f71.0310031252.76fe8b86@posting.google.com>


Thanks...

I created a function:

CREATE OR REPLACE FUNCTION FX_DATETIME(adate number,atime number) RETURN date IS

  v_output date;
  mo VARCHAR(2);
  da VARCHAR(2);
  yr VARCHAR(2);
  hh VARCHAR(2);
  mm VARCHAR(2);
  ss VARCHAR(2);

BEGIN

  mo := substr(to_char(adate),4,2);
  da := substr(to_char(adate),6,2);
  yr := substr(to_char(adate),2,2);
  hh := substr(lpad(to_char(atime),6,0),1,2);
  mm := substr(lpad(to_char(atime),6,0),3,2);
  ss := substr(lpad(to_char(atime),6,0),5,2);
  

 v_output := to_date(mo || '/' || da || '/' || yr || ' ' || hh || ':' || mm || ':' || ss,'MM/DD/YYYY HH24:MI:SS');  

  RETURN v_output;
END;
/


It works fine with just returning the value in the columns selected but not when used in the WHERE clause - I get no rows returned?

e.g.

this works and returns rows with date column date_time

select t2.unum as username, t1.queue_time as total_queuetime,t1.orig_date,t1.orig_time, FX_DATETIME(t1.orig_date,t1.orig_time) as date_time from detail t1, acc.users t2 where t1.orig_date = 1031001;

but this returns no rows...

select t2.unum as username, t1.queue_time as total_queuetime,t1.orig_date,t1.orig_time, FX_DATETIME(t1.orig_date,t1.orig_time) as date_time from detail t1, acc.users t2 where FX_DATETIME(t1.orig_date,t1.orig_time) >= To_date('10/01/2003 07:00:00','MM/DD/YYYY HH24:MI:SS');

what should be the correct syntax on the where clause?? - I've tried various methods but nothing works..

TIA - Andrew. Received on Fri Oct 03 2003 - 15:52:06 CDT

Original text of this message

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