Well, this fits the newly clarifed specs. :)
-- find the first wednesday of any month
alter session set nls_date_format = 'mm/dd/yyyy';
define testdate = '08/06/2003'
select
-- 1 means that the first wednesday of this month is in the future
-- 0 means that today is the first wednesday of the month
-- -1 means that the first wednesday of the month is in the past
decode( sign (
trunc(next_day(last_day(add_months('&&testdate',-1)),'WED'),'dd')
-
trunc(to_date('&&testdate'),'dd')
),
1, next_day(last_day(add_months('&&testdate',-1)),'WED'),
next_day(last_day('&&testdate'),'WED')
)
from dual
/
Josh Collier <[EMAIL PROTECTED]>
Sent by: [EMAIL PROTECTED]
07/22/2003 02:24 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
Subject: RE: Job to run first Wednesday
It does appear wrong to me, maybe I am missing something.
if sysdate = August 1, then function should return August 6th (the first
Wed
in August) not September 3. By this logic the job would not run in August.
Josh
-----Original Message-----
Sent: Tuesday, July 22, 2003 1:30 PM
To: Multiple recipients of list ORACLE-L
Are you saying 03-SEP-03 is wrong?
Igor Neyman, OCP DBA
[EMAIL PROTECTED]
-----Original Message-----
Rachel Carmichael
Sent: Tuesday, July 22, 2003 3:15 PM
To: Multiple recipients of list ORACLE-L
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
--- [EMAIL PROTECTED] wrote:
> Hmm... much more elegant than mine, and everyone elses.
>
> Guess I better RTFM the next_day function.
>
>
>
>
>
>
> "Mercadante, Thomas F" <[EMAIL PROTECTED]>
> Sent by: [EMAIL PROTECTED]
> 07/22/2003 12:34 PM
> Please respond to ORACLE-L
>
>
> To: Multiple recipients of list ORACLE-L
> <[EMAIL PROTECTED]>
> 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 first
> Wednesday of every month? Is this even possible? I have been trying
> to
> noodle it thru for a week to no avail.
>
> tia,
>
> Josh
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Josh Collier
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Mercadante, Thomas F
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author:
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
__________________________________
Do you Yahoo!?
Yahoo! SiteBuilder - Free, easy-to-use web site design software
http://sitebuilder.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Rachel Carmichael
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Igor Neyman
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Josh Collier
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
