Thursday, 25 February 2016

How to add a day/hour/minute/second to a date value in Oracle

The SYSDATE pseudo-column shows the current system date and time. Adding 1 to SYSDATE will advance the date by 1 day. 
Some other Examples:
DescriptionDate Expression
NowSYSDATE
Tomorow/ next daySYSDATE + 1
Seven days from nowSYSDATE + 7
One hour from nowSYSDATE + 1/24
Three hours from nowSYSDATE + 3/24
An half hour from nowSYSDATE + 1/48
10 minutes from nowSYSDATE + 10/1440
30 seconds from nowSYSDATE + 30/86400
Tomorrow at 12 midnightTRUNC(SYSDATE + 1)
Tomorrow at 8 AMTRUNC(SYSDATE + 1) + 8/24
Next Monday at 12:00 noonNEXT_DAY(TRUNC(SYSDATE), 'MONDAY') + 12/24
First day of the month at 12 midnightTRUNC(LAST_DAY(SYSDATE ) + 1)
The next Monday, Wednesday or Friday at 9 a.mTRUNC(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;           

No comments:

Post a Comment