Add and subtract operations for Oracle dates

Source: Internet
Author: User
Tags add hours

Both date and timestamp can be added and reduced.
There are three ways to manipulate different time types for the current date Gagnin, month, day, hour, minute, and second:

1 add hours, minutes and seconds using built-in functions Numtodsinterval
2 plus a simple number to increase the day
3 use built-in function add_months to increase year and month

Cases:
Add one hours to the current date:
Sql> Select Sysdate, Sysdate+numtodsinterval (1, ' Hour ') from dual;

Sysdate Sysdate+numtodsinte
——————- ——————-
2010-10-14 21:38:19 2010-10-14 22:38:19
Add 50 minutes to the current date
Sql> Select Sysdate, Sysdate+numtodsinterval (' minute ') from dual;

Sysdate Sysdate+numtodsinte
——————- ——————-
2010-10-14 21:39:12 2010-10-14 22:29:12
Add 45 seconds to the current date
Sql> Select Sysdate, Sysdate+numtodsinterval ("second") from dual;

Sysdate Sysdate+numtodsinte
——————- ——————-
2010-10-14 21:40:06 2010-10-14 21:40:51
Add 3 days to the current date
Sql> Select Sysdate, sysdate+3 from dual;

Sysdate sysdate+3
——————- ——————-
2010-10-14 21:40:46 2010-10-17 21:40:46
Add 4 months to the current date
Sql> Select Sysdate, Add_months (sysdate,4) from dual;

Sysdate add_months (Sysdate,
——————- ——————-
2010-10-14 21:41:43 2011-02-14 21:41:43

2 year increase in current date
Sql> Select Sysdate, Add_months (sysdate,12*2) from dual;

Sysdate add_months (Sysdate,
——————- ——————-
2010-10-14 21:42:17 2012-10-14 21:42:17

The operation method of timestamp is similar to the above;
Ask for the difference of two dates:
Example: To find the difference between the 21:23:34 and the current time in 2007-5-23.
Sql> Select Sysdate-to_date (' 20070523 21:23:34′, ' yyyy-mm-dd hh24:mi:ss ') DT from
Dual;

Dt
———-
1240.01623
If the two date is directly subtracted, the result is a data type, we may want to get two date difference in the expression form:
* * * * * * * * * * * *:* *:* *
Sql> SELECT Numtoyminterval (Months_between (DT1, DT2), ' month ') Mon,
2 Numtodsinterval (dt1-(Add_months (Dt2,trunc (Months_between, DT1))
"Day") Day
3 from (SELECT sysdate DT1,
4 to_date (' 20070523 21:23:34′, ' yyyy-mm-dd hh24:mi:ss ') DT2
5 from DUAL)
6;

MON Day
—————- ———————-
+000000003-04 +000000021 00:40:15.999999999

namely: 3 years 4 months 21 days 00:40:15.99999999

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.