Monthly Archives: April 2014

The next half hour

I have a job in Oracle that runs every 30 minutes (during work hours), and wanted to schedule the first run at the next “xx:30” mark on the clock. Nothing all that exciting, but took me a few minutes to figure out the logic and I thought I would share.

Line of interest from my call to the scheduler:

 start_date      =>   CEIL( (SYSDATE - trunc(SYSDATE)) * 48 )/ 48 + trunc( SYSDATE )
 

Basic demonstration of the calculation:

SQL> ALTER SESSION SET NLS_DATE_FORMAT = "MM/DD/YYYY HH:MI AM";
  
Session altered.
  
SQL> SELECT SYSDATE FROM DUAL;
  
SYSDATE
-------------------
04/02/2014 12:11 PM
  
SQL> SELECT CEIL( (SYSDATE - trunc(SYSDATE)) * 48 )/ 48 + trunc( SYSDATE ) FROM DUAL;
  
CEIL((SYSDATE-TRUNC
-------------------
04/02/2014 12:30 PM
Advertisements