Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: need help creating procedure to query date/time range
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