Kashif Masood Hashmi Blog

Date Time Queries

SELECT 

1,'EVERY DAY ',SYSDATE+1 SYS_DATE , Systimestamp+1 SYS_TIMESTAMP, 

SYSDATE + INTERVAL '1' DAY SYSDATE_INTERVAL, SYSTIMESTAMP + INTERVAL '1' DAY SYSTIMESTAMP_INTERVAL

FROM DUAL 

UNION

SELECT

2,'MIDNIGHT EVERY NIGHT ',TRUNC(SYSDATE)+1 , TRUNC(Systimestamp)+1, 

TRUNC(SYSDATE) + INTERVAL '1' DAY, TRUNC(SYSTIMESTAMP) + INTERVAL '1' DAY

FROM DUAL

UNION

SELECT

3,'6:00 AM EVERY DAY ',TRUNC(SYSDATE)+1+6/24 , TRUNC(Systimestamp)+1+6/24, 

TRUNC(SYSDATE) + INTERVAL '1 6' DAY TO HOUR, TRUNC(SYSTIMESTAMP) + INTERVAL '1 6' DAY TO HOUR

FROM DUAL

UNION

SELECT

4,'EVERY HOUR ',SYSDATE+1/24 , SYSTIMESTAMP+1/24, 

SYSDATE + INTERVAL '1' HOUR, SYSTIMESTAMP + INTERVAL '1' HOUR

FROM DUAL

UNION

SELECT

5,'EVERY HOUR ON THE HOUR ',TRUNC(SYSDATE,'HH24')+1/24 , TRUNC(Systimestamp,'HH24')+1/24, 

TRUNC(SYSDATE,'HH24') + INTERVAL '1' HOUR, TRUNC(SYSTIMESTAMP,'HH24') + INTERVAL '1' HOUR

FROM DUAL

UNION

SELECT

6,'EVERY MINUTE ',TRUNC(SYSDATE)+1/24/60 , TRUNC(Systimestamp)+1/24/60, 

TRUNC(SYSDATE) + INTERVAL '1' MINUTE, TRUNC(SYSTIMESTAMP) + INTERVAL '1' MINUTE

FROM DUAL

UNION

SELECT

7,'EVERY MINUTE ON THE MINUTE ',TRUNC(SYSDATE,'MI')+1/24/60 , TRUNC(Systimestamp,'MI')+1/24/60, 

TRUNC(SYSDATE,'MI') + INTERVAL '1' MINUTE, TRUNC(SYSTIMESTAMP,'MI') + INTERVAL '1' MINUTE

FROM DUAL

UNION

SELECT

8,'EVERY MONDAY AT 09:00AM ',TRUNC(NEXT_DAY(SYSDATE,'MONDAY'))+9/24, TRUNC(NEXT_DAY(SYSTIMESTAMP,'MONDAY'))+9/24, 

TRUNC(NEXT_DAY(SYSDATE,'MONDAY')) + INTERVAL '9' HOUR, TRUNC(NEXT_DAY(SYSTIMESTAMP,'MONDAY'))+ INTERVAL '9' HOUR

FROM DUAL

UNION

SELECT

9,'EVERY MONDAY,WEDNESDAY,FRIDAY AT 06:00AM ', 

TRUNC(LEAST(NEXT_DAY(SYSDATE,       'MONDAY'),NEXT_DAY(SYSDATE,       'WEDNESDAY'), NEXT_DAY(SYSDATE,      'FRIDAY')))+6/24,

TRUNC(LEAST(NEXT_DAY(SYSTIMESTAMP,  'MONDAY'),NEXT_DAY(SYSTIMESTAMP,  'WEDNESDAY'), NEXT_DAY(SYSTIMESTAMP, 'FRIDAY')))+6/24,

TRUNC(LEAST(NEXT_DAY(SYSDATE,       'MONDAY'),NEXT_DAY(SYSDATE,       'WEDNESDAY'), NEXT_DAY(SYSDATE,      'FRIDAY'))) + INTERVAL '6' HOUR, 

TRUNC(LEAST(NEXT_DAY(SYSTIMESTAMP,  'MONDAY'),NEXT_DAY(SYSTIMESTAMP,  'WEDNESDAY'), NEXT_DAY(SYSTIMESTAMP, 'FRIDAY'))) + INTERVAL '6' HOUR

FROM DUAL

UNION

SELECT

10,'FIRST MONDAY OF EACH QUARTER ',

NEXT_DAY(ADD_MONTHS(TRUNC(SYSDATE,'Q'),3),'MONDAY'), 

NEXT_DAY(ADD_MONTHS(TRUNC(SYSTIMESTAMP,'Q'),3),'MONDAY'),

TO_DATE(NULL), TO_DATE(NULL)

FROM DUAL

ORDER BY 1;

Output