Hi, I just RTFM, squared away now.
Josh
-Original Message-
Sent: Tuesday, July 22, 2003 12: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
Title: RE: Job to run first Wednesday
SELECT NEXT_DAY(ADD_MONTHS(TRUNC(SYSDATE,'MM')-1,rnum-1),'WED')
FROM (SELECT ROWNUM rnum FROM ALL_OBJECTS WHERE ROWNUM 12)
/
for next 12 months including this month
Raj
Josh,
How about NEXT_DAY(TRUNC(LAST_DAY(SYSDATE)+1),'WEDNESDAY')+6/24? This
should run your DBMS_JOB's the first Wednesday of the following month at
6:00am.
Abey.
-Original Message-
Sent: Tuesday, July 22, 2003 3:14 PM
To: Multiple recipients of list ORACLE-L
Greetings,
How can I
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
Here's a bit of code that will always find the first wednesday of the
following month:
-- find the first wednesday of any month
alter session set nls_date_format = 'mm/dd/';
define testdate = '09/03/2003'
select
trunc(add_months('testdate',1),'mm') +
decode(
]
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
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
or simply
SELECT NEXT_DAY(ADD_MONTHS(TRUNC(to_date('31-dec-2002'), 'MM')-1, 1),
'WEDNESDAY')
FROM dual
/
-Original Message-
Sent: Tuesday, July 22, 2003 3:50 PM
To: Multiple recipients of list ORACLE-L
Here's a bit of code that will always find the first wednesday of the
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
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
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
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/')),'WED
:
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
:
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/')),'WED') from dual
SQL /
NEXT_DAY(
-
03-SEP-03
--- [EMAIL PROTECTED] wrote:
Hmm
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
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
ORACLE-L
[EMAIL PROTECTED]
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/')),'WED') from
dual
SQL /
NEXT_DAY
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
? :)
Jared
Rachel Carmichael [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
07/22/2003 01:14 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
cc:
Subject:RE: Job to run first Wednesday
don't rush off to use
'),
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
by: [EMAIL PROTECTED]
07/22/2003 01:14 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
cc:
Subject:RE: Job to run first Wednesday
don't rush off to use it. I tried it, substituting August 1 and
got
September
I know some solutions have already been posted. I will add this one however. Some of
the examples posted have the drawback that they assume your NLS date language is
English.
The formula below uses the fact that 1 January 2003 is a Wednesday.
The expression to find the first Wednesday of the
PROTECTED]
Sent by: [EMAIL PROTECTED]
07/22/2003 01:14 PM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L
[EMAIL PROTECTED]
cc:
Subject:RE: Job to run first Wednesday
don't rush off to use it. I tried it, substituting August 1
22 matches
Mail list logo