Oracle 12c: Running scheduled job with date interval

Question

I have the following script:

SELECT 
USERNAME
ITEM
BUSINESS
ADDED
FROM BUSINESS
WHERE 
BUSINESS IN ('X','Y','Z')
AND ADDED BETWEEN TO_DATE('8/30/2019 00:00:00', 'MM/DD/YYYY HH24:MI:SS') AND TO_DATE('9/30/2019 11:59:59', 'MM/DD/YYYY HH24:MI:SS')

The date interval is correct but I want the dates to change after each scheduled job. The scheduled job script is as follows:

SYS.DMBS_SCHEDULER.CREATE_JOB
     (
          job_name => 'test'
         ,start_date => TO_TIMESTAMP_TZ('2019/10/23 09:00:00.000000 US/Eastern', 'yyyy/mm/dd hh24:mi:ss.ff tzr')
         ,repeat_interval => 'FREQ=MONTHLY' INTERVAL=1'
         ,end_date => NULL

etc. etc.

I believe I only need to change the date format in the script but how do I ensure the script date moves 1 month forward after each scheduled job run? Thanks in advance.

score 0 · Answer 1 · answered Nov 18 '19 at 09:17

I'm not entirely positive of your criteria:

TO_DATE('8/30/2019 00:00:00', 'MM/DD/YYYY HH24:MI:SS') AND TO_DATE('9/30/2019 11:59:59', 'MM/DD/YYYY HH24:MI:SS')

because that could be "Always the 30th", which means problems in Feburary, or it could mean, "All of the previous month". I'm going to assume the latter, in which case you do not need the dates hard coded, you can achieve this with manipulation of sysdate, ie,

ADDED >= add_Months(trunc(sysdate,'MM'),-1) AND
ADDED < trunc(sysdate,'MM')

Oracle 12c: Running scheduled job with date interval

1 Answers1