Some other Examples:
| Description | Date Expression |
|---|---|
| Now | SYSDATE |
| Tomorow/ next day | SYSDATE + 1 |
| Seven days from now | SYSDATE + 7 |
| One hour from now | SYSDATE + 1/24 |
| Three hours from now | SYSDATE + 3/24 |
| An half hour from now | SYSDATE + 1/48 |
| 10 minutes from now | SYSDATE + 10/1440 |
| 30 seconds from now | SYSDATE + 30/86400 |
| Tomorrow at 12 midnight | TRUNC(SYSDATE + 1) |
| Tomorrow at 8 AM | TRUNC(SYSDATE + 1) + 8/24 |
| Next Monday at 12:00 noon | NEXT_DAY(TRUNC(SYSDATE), 'MONDAY') + 12/24 |
| First day of the month at 12 midnight | TRUNC(LAST_DAY(SYSDATE ) + 1) |
| The next Monday, Wednesday or Friday at 9 a.m | TRUNC(LEAST(NEXT_DAY(sysdate,''MONDAY' ' ),NEXT_DAY(sysdate,''WEDNESDAY''), NEXT_DAY(sysdate,''FRIDAY'' ))) + (9/24) |
Ex:
select to_char(sysdate,'dd-mon-yyyy hh:mi:ss') DTE, to_char(sysdate - 1/24,'dd-mon-yyyy hh:mi:ss') DTE_BFR_1Hr from dual;
Now, how to print difference between these 2 dates in Hr:
SELECT to_number( to_char(to_date('1','J') + (sysdate - (sysdate - 1/24)), 'J') - 1) days,
to_char(to_date('00:00:00','HH24:MI:SS') + (sysdate - (sysdate - 1/24)), 'HH24:MI:SS') time,
to_number(to_char(to_date('00:00:00','HH24:MI:SS') + (sysdate - (sysdate - 1/24)), 'HH24')) time2
from dual;
