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).

Reply via email to