Oracle PL/SQL Tutorial/Date Timestamp Functions/ADD MONTHS
Содержание
- 1 ADD_MONTHS(x, y) gets the result of adding y months to x.
- 2 Add Negative time
- 3 Combine ADD_MONTHS() function with To_Date
- 4 Combine ADD_MONTHS() with TO_CHAR() and TO_DATE
- 5 SELECT ADD_MONTHS("01-JAN-2005", 3) FROM dual;
- 6 SELECT ADD_MONTHS("01-JAN-2005", -3) FROM dual; (2)
- 7 SELECT ADD_MONTHS("28-NOV-05", 1) FROM DUAL;
- 8 SELECT ADD_MONTHS("29-NOV-05", 1) FROM DUAL;
- 9 SELECT ADD_MONTHS("30-NOV-05", 1) FROM DUAL;
- 10 SELECT ADD_MONTHS("31-DEC-05", -1) FROM DUAL;
- 11 Subtract 13 months from the January 1, 2005
ADD_MONTHS(x, y) gets the result of adding y months to x.
If y is negative, y months are subtracted from x.
The following example adds 13 months to January 1, 2005:
<source lang="sql">
SQL> SELECT ADD_MONTHS("01-JAN-2005", 13)
2 FROM dual;
ADD_MONTH
01-FEB-06 SQL></source>
Add Negative time
<source lang="sql">
SQL> SQL> SQL> SELECT TO_CHAR(TO_CHAR(ADD_MONTHS(SYSDATE, -23), "ddMONyyyy")) "- 23 mon"
2 FROM dual;
- 23 mon
29JUN2005 SQL> SQL></source>
Combine ADD_MONTHS() function with To_Date
<source lang="sql">
SQL> SQL> SELECT ADD_MONTHS(TO_DATE("01-JAN-2005 19:15:26",
2 "DD-MON-YYYY HH24:MI:SS"), 2) 3 FROM dual;
ADD_MONTH
01-MAR-05 SQL></source>
Combine ADD_MONTHS() with TO_CHAR() and TO_DATE
<source lang="sql">
SQL> SQL> SQL> SELECT TO_CHAR(ADD_MONTHS(TO_DATE("01-JAN-2005 19:15:26",
2 "DD-MON-YYYY HH24:MI:SS"), 2), "DD-MON-YYYY HH24:MI:SS") 3 FROM dual;
TO_CHAR(ADD_MONTHS(T
01-MAR-2005 19:15:26 SQL></source>
SELECT ADD_MONTHS("01-JAN-2005", 3) FROM dual;
<source lang="sql">
SQL> SELECT ADD_MONTHS("01-JAN-2005", 3) FROM dual; ADD_MONTH
01-APR-05 SQL> SQL></source>
SELECT ADD_MONTHS("01-JAN-2005", -3) FROM dual; (2)
<source lang="sql">
SQL> SELECT ADD_MONTHS("01-JAN-2005", -3) FROM dual; ADD_MONTH
01-OCT-04</source>
SELECT ADD_MONTHS("28-NOV-05", 1) FROM DUAL;
<source lang="sql">
ADD_MONTH
28-DEC-05</source>
SELECT ADD_MONTHS("29-NOV-05", 1) FROM DUAL;
<source lang="sql">
ADD_MONTH
29-DEC-05</source>
SELECT ADD_MONTHS("30-NOV-05", 1) FROM DUAL;
<source lang="sql">
ADD_MONTH
31-DEC-05</source>
SELECT ADD_MONTHS("31-DEC-05", -1) FROM DUAL;
<source lang="sql">
ADD_MONTH
30-NOV-05 SQL></source>
Subtract 13 months from the January 1, 2005
<source lang="sql">
SQL> SQL> SELECT ADD_MONTHS("01-JAN-2005", -13) FROM dual; ADD_MONTH
01-DEC-03 SQL></source>