Oracle Date manipulation

 

Oracle add hr  to date
Oracle add Min to date
Oracle add sec to date

1/24=one hour , 1/1440=one minute , 1/86400=one second


1/24/60/60   One second
7/24/60/60   Seven seconds

7/24    7 hours
14/24   14 hours


TRUNC(SYSDATE+1/24,'HH') = 1hr




In 1 day 24 hr



select 24*60 "In 24hr ->>Min " from dual;

In 24hr ->>Min
---------------
           1440



SQL> select 24*60*60 "In 24hr ->>second  " from dual;

In 24hr ->>second
-------------------
              86400



SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD-MM-YYYY HH24:MI:SS';

Session altered.




SQL> SELECT   SYSDATE,SYSDATE+1/24 FROM DUAL;  ---- 1 hr add

SYSDATE             SYSDATE+1/24
------------------- -------------------
28-11-2011 14:09:47 28-11-2011 15:09:47


SQL> SELECT SYSDATE, SYSDATE+1/1440 FROM DUAL;  ---- add one min

SYSDATE             SYSDATE+1/1440
------------------- -------------------
28-11-2011 14:10:47 28-11-2011 14:11:47


SQL> SELECT SYSDATE , SYSDATE+1/86400 FROM DUAL;  ---- add one sec

SYSDATE             SYSDATE+1/86400
------------------- -------------------
28-11-2011 14:47:34 28-11-2011 14:47:35





http://anuj-singh.blogspot.com/2011/02/oracle-date-math-with-anuj.html