Re: DBMS_JOB

Very interesting Tim.

Here's a test of it.

alter session set nls_date_format = 'mm/dd/yyyy hh24:mi';

declare

   v_date date;
   v_test_date date;

   type date_t is table of date index by binary_integer;    v_date_table date_t;
begin

   v_date_table(1) := to_date('03/02/2005 00:29');
   v_date_table(2) := to_date('03/02/2005 00:30');
   v_date_table(3) := to_date('03/02/2005 00:31');
   v_date_table(4) := to_date('03/02/2005 12:29');
   v_date_table(5) := to_date('03/02/2005 12:30');
   v_date_table(6) := to_date('03/02/2005 12:31');

   for i in v_date_table.first .. v_date_table.last    loop

      dbms_output.put('Test Date: ' || v_date_table(i));

      SELECT
         CASE WHEN TO_CHAR(v_date_table(i)+(1/48),'HH24') BETWEEN '01' AND '12'
         THEN (TRUNC(v_date_table(i))+(25/48))
         ELSE (TRUNC(v_date_table(i) -(1/24))+(49/48))
         END
      into v_test_date
      from dual;

      dbms_output.put_line('    Next Date: ' || v_test_date);

   end loop;

end;
/

On Wed, 2 Mar 2005 13:32:47 -0800, Tim Hall <timhall1_at_gmail.com> wrote:
> I don't have time to test this, or fix parentheses errors on my CASE
> statement, etc., but how about something like:
>
> (CASE WHEN TO_CHAR(SYSDATE+(1/48),'HH24') BETWEEN '01' AND '12' THEN
> (TRUNC(SYSDATE)+(25/48))
> ELSE (TRUNC(SYSDATE -(1/24))+(49/48)) END)
>
> So if the current time is between 00:30:00 and 12:29:59, then the job
> runs at 12:30 in the afternoon today,
> else if the current time is not between 00:30:00 and 12:29:59, then
> the job runs at 12:30 at night (same day if current time is between
> 00:00:00 and 00:29:59, next day if current time is between 12:30:00
> and 23:59:59)
>
> Hope this helps -- Tim
>

-- 
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
--
http://www.freelists.org/webpage/oracle-l