RE: Job to run first Wednesday

From: Rudy Zung <rzung_at_printcafe.com>
Date: Tue, 22 Jul 2003 18:05:46 -0400
Message-Id: <25988.338988@fatcity.com>

Oh horrors! I was thinking that my solution was somewhat inelegant in the face of the LAST_DAY/NEXT_DAY solution, until Rachel Carmichael points out that if the the job was submitted at the beginning of the month where the first Wed of that month hasn't passed yet, the LAST_DAY/ NEXT_DAY query returns the following month's first Wed.

Anyway, the big CASE used by Igor Neyman and Dan Fink is sufficiently scaring me, that I'll repost my original solution, which should work in Rachel's scenario:

-----Original Message-----
From: Rudy Zung
Sent: Tuesday, July 22, 2003 3:50 PM
To: Multiple recipients of list ORACLE-L Subject: RE: Job to run first Wednesday

What an interesting question.

Alright, DBMS_JOB needs a function which returns the date/time on which the job will next run. Part of your function is to take consider the next 31 days (in case the current run of the job is somewhere random in the month and not on the first Wednesday of the current month) and identify the very next Wednesday, that happens to occur in the first 7 days of the month.

With that in mind:
select run_date

   from (select to_char(sysdate + rownum, 'dd-Mon-yyyy') run_date,
                to_char(sysdate + rownum, 'w') week_of_month,
                to_char(sysdate + rownum, 'd') day_of_week
            from all_objects
            where rownum <= 31)
   where week_of_month = 1 and
         day_of_week   = 4;

The inline makes use of a table/view that more or less is assumed to contain at least 31 objects in order to generate an offset to represent each day for the next month or so, which is added to the current sysdate. The inline view returns the date, week of month, and day of week for the upcoming 31 days (but necessarily excludes today if today happens to be the first Wed of this month, otherwise your job will always identify today as being the next scheduled run time, and never manage to find next month's first Wed).

The enclosing query then takes all the date information for the upcoming week, and limits it to the single record that occurs within the first week of the month, and is also the fourth day of the week (which is Wed.)

You may need to trunc() the run_date and do some other arithmatic to nail down a specific time of day for that job to run.

Go have fun.

...Rudy

-----Original Message-----
From: Igor Neyman [mailto:ineyman_at_perceptron.com] l Sent: Tuesday, July 22, 2003 5:59 PM
To: Multiple recipients of list ORACLE-L Subject: RE: Job to run first Wednesday

This should fix the flaw:

select CASE WHEN to_char(to_date('09/04/2003','MM/DD/YYYY'), 'DD') <= 6

            THEN CASE WHEN to_char(to_date('09/04/2003','MM/DD/YYYY'), 'DY') = 'WED'

                      THEN to_date('09/04/2003', 'MM/DD/YYYY')
                      ELSE CASE WHEN
to_char(next_day(to_date('09/04/2003', 'MM/DD/YYYY'), 'WED'), 'DD') > 6
                                THEN
next_day(last_day(to_date('09/04/2003','MM/DD/YYYY')),'WED')
                                ELSE next_day(to_date('09/04/2003',
'MM/DD/YYYY'), 'WED')
                           END
                 END
            ELSE
next_day(last_day(to_date('09/04/2003','MM/DD/YYYY')),'WED')
       END

from dual;

Igor Neyman, OCP DBA
ineyman_at_perceptron.com

-----Original Message-----
Daniel Fink
Sent: Tuesday, July 22, 2003 4:04 PM
To: Multiple recipients of list ORACLE-L

Jared,

        If you will closely examine the specs, the assumption is not documented. Therefore, the application code must take into account that the 1st Wednesday of the month may still be in the future.

select CASE WHEN to_char(to_date('08/07/2003','MM/DD/YYYY'), 'DD') <= 6

            THEN CASE WHEN to_char(to_date('08/07/2003','MM/DD/YYYY'), 'DY') = 'WED'

                      THEN to_date('08/07/2003', 'MM/DD/YYYY')
                      ELSE next_day(to_date('08/07/2003', 'MM/DD/YYYY'),
'WED')
                 END
            ELSE
next_day(last_day(to_date('08/07/2003','MM/DD/YYYY')),'WED')
       END

from dual;

        Of course, there is a flaw in this logic. Can anyone spot it? It's particularly nasty...

Daniel

Jared.Still_at_radisys.com wrote:
>
> Rachel,
>
> The assumption is that the current day is already >= the first
wednesday
> of the month,
> making it useful for the dbms_job interval.
>
> Did you read the specs? :)
>
> Jared
>
> Rachel Carmichael <wisernet100_at_yahoo.com>
> Sent by: ml-errors_at_fatcity.com
> 07/22/2003 01:14 PM
> Please respond to ORACLE-L
>
>
> To: Multiple recipients of list ORACLE-L
<ORACLE-L_at_fatcity.com>
> cc:
> Subject: RE: Job to run first Wednesday
>
> don't rush off to use it..... I tried it, substituting August 1 and
got
> September.
>
> 1* select
> next_day(last_day(to_date('08/01/2003','MM/DD/YYYY')),'WED') from dual
> SQL> /
>
> NEXT_DAY(
> ---------
> 03-SEP-03
>
> --- Jared.Still_at_radisys.com wrote:
> > Hmm... much more elegant than mine, and everyone elses.
> >
> > Guess I better RTFM the next_day function.
> >
> >
> >
> >
> >
> >
> > "Mercadante, Thomas F" <NDATFM_at_labor.state.ny.us>
> > Sent by: ml-errors_at_fatcity.com
> > 07/22/2003 12:34 PM
> > Please respond to ORACLE-L
> >
> >
> > To: Multiple recipients of list ORACLE-L
> > <ORACLE-L_at_fatcity.com>
> > cc:
> > Subject: RE: Job to run first Wednesday
> >
> >
> > Josh,
> >
> > With the following functions, you could probably get it to work:
> >
> > select next_day(last_Day(sysdate),'WED') from dual
> >
> > This (today) returns Wed, August 6th.
> >
> > Tom Mercadante
> > Oracle Certified Professional
> >
> >
> > -----Original Message-----
> > Sent: Tuesday, July 22, 2003 3:14 PM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > Greetings,
> >
> > How can I set the interval in my dbms job to have it run on the
Received on Tue Jul 22 2003 - 17:05:46 CDT