Pages

Thursday, July 5, 2012

PL/SQL: Oracle Date/Time Calculations

This is a very simple blog which is just a list of Oracle Date/Time calculations (i.e. every two hours, every day, 8am every day, etc). These formulas are intended to help you calculate the "next" date for a given requirement.

Midnight;
TRUNC(SYSDATE)+1

Tomorrow at 7am;
TRUNC(SYSDATE)+1 + 7/24

Every two hours (on the hour);
TRUNC(SYSDATE, 'HH') + 2/24

Every two hours (on the hour) between 8am and 6pm every day;
CASE WHEN TRUNC(SYSDATE, 'HH') + 2/24 <= TRUNC(SYSDATE) + 19/24 THEN TRUNC(SYSDATE, 'HH') + 2/24 ELSE TRUNC(SYSDATE)+1 + 8/24 END





No comments: