Wednesday, May 20, 2020

Date time logic in Oracle - Scheduling jobs or events

This is for my note only, some times I need these schedule data time logic for Jobs in Oracle but can't recall quickly. so just put these things together here.


Execute daily      -                        'SYSDATE + 1'
Execute every 6 hours     -           'SYSDATE + 6/24'
Execute every 20 minutes -         'SYSDATE + 20/1440'
Execute every 40 seconds -         'SYSDATE + 40/86400'
Execute every 7 days -                'SYSDATE + 7'
Every day at 12:00 midnight -    'TRUNC(SYSDATE + 1)'
Every day at 10:00 p.m. -             'TRUNC(SYSDATE + 1) + 22/24'
Every Tuesday at 11:00 noon -   'NEXT_DAY(TRUNC(SYSDATE), "TUESDAY") + 11/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'
Last day of the year at 11:00 p.m.-     'TRUNC(ADD_MONTH(SYSDATE + 2/24,12),'Y') - 1/24'

Every Monday, Wednesday and Friday at 10:00 p.m. -  
         'TRUNC(LEAST(NEXT_DAY(SYSDATE, "MONDAY"), NEXT_DAY(SYSDATE, "WEDNESDAY"), NEXT_DAY(SYSDATE, "FRIDAY"))) + 22/24'