[Oracle] 오라클 날짜, 시간, 분, 초 등 더하기

Posted by RAY.D
2015. 4. 27. 11:33 Database/Oracle
336x280(권장), 300x250(권장), 250x250, 200x200 크기의 광고 코드만 넣을 수 있습니다.




How does one add a day/hour/minute/second to a date value?




SQL> select sysdate, sysdate+1/24, sysdate +1/1440, sysdate + 1/86400 from dual;

SYSDATE              SYSDATE+1/24         SYSDATE+1/1440       SYSDATE+1/86400
-------------------- -------------------- -------------------- --------------------
03-Jul-2002 08:32:12 03-Jul-2002 09:32:12 03-Jul-2002 08:33:12 03-Jul-2002 08:32:13

The following format is frequently used with Oracle Replication:

select sysdate NOW, sysdate+30/(24*60*60) NOW_PLUS_30_SECS from dual;

NOW                  NOW_PLUS_30_SECS
-------------------- --------------------
03-JUL-2005 16:47:23 03-JUL-2005 16:47:53

Here are a couple of 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)