Xref: alice comp.databases.oracle.tools:24371
Path: alice!news-feed.fnsi.net!netnews.com!news.maxwell.syr.edu!nntp2.dejanews.com!nnrp1.dejanews.com!not-for-mail
From: RTProffitt@beckman.com
Newsgroups: comp.databases.oracle.tools
Subject: Re: plsql date generator, sample code?
Date: Wed, 05 May 1999 16:37:33 GMT
Organization: Deja News - The Leader in Internet Discussion
Lines: 32
Message-ID: <7gps4c$pou$1@nnrp1.dejanews.com>
References: <7gn7tu$gba$1@nnrp1.dejanews.com>
X-Article-Creation-Date: Wed May 05 16:37:33 1999 GMT
X-Http-User-Agent: Mozilla/2.0 (compatible; MSIE 3.02; Windows NT)
X-Http-Proxy: 1.0 x15.dejanews.com:80 (Squid/1.1.22) for client 134.217.237.30

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:

-- 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
Beckman/Coulter
RTProffitt@beckman.com

-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/       Search, Read, Discuss, or Start Your Own