Re: plsql date generator, sample code?

From: Andy Hardy <>
Date: Wed, 5 May 1999 21:35:14 +0100
Message-ID: <>

In article <7gps4c$pou$>, writes
>Dates in Oracle are pretty easy to work with.
>Sysdate (or Date Field) + 30, +60, etc... is easy to do
>See sample:
> select sysdate, sysdate-6 from dual;
> 05-MAY-99 29-APR-99
>We had a need to calculate if user was performing an
>action within 6 minutes (1 tenth of an hour) and disallow
>it if so. I had to find the "jobstart" time then find the
>previous "jobstart" time and see if the difference is
>within 6 minutes. Here is some code:

Daft question, why didn't you just put all the timing in the select statement? e.g.

CURSOR csr_done_it_before (p_this_date DATE) select 'done it before'
  from job_start_log
 where time_stamp > p_this_date - ((1/(24*60)) * 6)    and etc.

OPEN csr_done_it_before (v_this_time_stamp); FETCH csr_done_it_before INTO v_dummy;
IF csr_done_it_before%FOUND THEN

        -- tell the user off!

CLOSE csr_done_it_before;

>-- Now check times between jobstarts -- ..... cursor c1(cSerial varchar2) is
>select time_stamp from job_start_log ...etc ...... open c1(serial); fetch c1
>into vDate; <-- go to the log and get previous "jobstart" close c1; .....
>-- Positive numbers if jobstarted today. if ((Vdate - trunc(sysdate))*24) >
>0 then <- this trunc the minutes and seconds. DAYS*24=hours -- today, check
>some more. --- -- if same hour, check tenths of hour. -- calculate tenths
>of hours and compare. Can't be same tenth. if (to_char(vDate,'hh24') =
>to_char(sysdate,'hh24')) then <-- same hour? if
>(trunc(to_number(to_char(vDate,'mi'))/6) -
>trunc(to_number(to_char(sysdate,'mi'))/6) ) = 0 then <-- same tenth hour?
>-- not allowed - raise_application_error(-20101, '>>May Not Jobstart in the
>Same Tenth Hour. '|| 'Wait for 6 minutes.<<'); end if; end if; end if;
>Robert Proffitt
>-----------== Posted via Deja News, The Discussion Network ==----------
> Search, Read, Discuss, or Start Your Own

Andy Hardy. PGP key available on request
Received on Wed May 05 1999 - 22:35:14 CEST

Original text of this message