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

acgoodall_at_ev1.net (Andrew) wrote in message news:<f8344f71.0310031252.76fe8b86_at_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.

Andrew,

The syntax of your WHERE CLAUSE is fine.

Your FUNCTION has the problem.

Try to simplify it. You don't need all those slashes and concatenates. They only serve to hide the fact that you pass a 2digit year to a 4digit year format. Use something simpler like

   datepart := lpad(to_char(adate),7,0);    timepart := lpad(to_char(atime),6,0);

   v_output:= to_date(datepart || timepart, 'CYYMMDDHH24MISS') ;

Both converting the numeric date to CHAR and the to_date() format string nee work, but you should not need 7 lines of code and 7 local variables to solve this problem.

HTH
  Ed Prochak
  Magic Interface, Ltd.
  440-498-3700
Database, web site, and application development WEB Site and application hosting Received on Tue Oct 07 2003 - 12:21:46 CDT