Date Arithmetic

Action		Interval Time
Execute daily		'SYSDATE + 1'
Execute every 4 hours		'SYSDATE + 4/24'
Execute every 10 minutes		'SYSDATE + 10/1440'
Execute every 30 seconds		'SYSDATE + 30/86400'
Execute every 7 days		'SYSDATE + 7'
Do no re-execute and remove job		NULL

NOTE: Remember that job intervals expressed as shown in the previous table do not guarantee that the next execution will happen at a specific day or time, only that the spacing between executions will be at least that specified. For instance, if a job is first executed at 12:00 p.m. with in interval of 'SYSTEM + 1', it will be scheduled to execute the next day at 12:00 p.m. However, if a user executes the job manually at 4:00 p.m. the next day using DBMS_JOB.RUN, then it will be rescheduled for execution at 4:00 p.m. the next day. Another possibility is that the database is down or the job queue so busy that the job cannot be executed exactly at the time scheduled. In this case, the job will run as soon as it can, but the execution time will have migrated away from the original submission time due to the later execution. This "drift" in next execution times is characteristic of jobs with simple interval expressions.




Jobs with type 2 execution requirements involve more complex interval date expressions, as see in the following table.

Action	Interval Time
Every day at 12:00 midnight	TRUNC(SYSDATE + 1)
Every day at 8:00 p.m.	TRUNC(SYSDATE + 1) + 20/24
Every Tuesday at 12:00 noon	NEXT_DAY(TRUNC(SYSDATE), "TUESDAY") + 12/24
First day of the month at midnight	TRUNC(LAST_DAY(SYSDATE) + 1)
Last day of the quarter at 11:00 p.m.	TRUNC(ADD_MONTH(SYSDATE + 2/24,3),'Q') - 1/24
Every Monday, Wednesday and Friday at 9:00 p.m.	TRUNC(LEAST(NEXT_DAY(SYSDATE, "MONDAY"), NEXT_DAY(SYSDATE, "WEDNESDAY"), NEXT_DAY(SYSDATE, "FRIDAY"))) + 21/24

WHERE RD.DATE_CREATED >= (TRUNC(ADD_MONTHS(SYSDATE,-1),'MM') + 19) AND RD.DATE_CREATED <= (TRUNC(SYSDATE ,'MM') + 19) -- Between 20th of current and previous month


SELECT
TO_CHAR((NEXT_DAY(TRUNC(SYSDATE-14), 'MONDAY') + 0 ), 'DD-MON-YYYY HH24:MI:SS') AS "Last2L Monday" ,
TO_CHAR((NEXT_DAY(TRUNC(SYSDATE-7), 'MONDAY') + 0 ), 'DD-MON-YYYY HH24:MI:SS') AS "Last Monday"
FROM DUAL; --- between last to Mondays