In addition to add_months, you can also use INTERVAL:
- Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.7.0
- ConnectedAsXxpo
- SQL>
- SQL> SELECT to_char (SYSDATE,'Yyyy/MM/DD HH24: MI: ss') Now FROM dual;
- NOW
- -------------------
- 06:22:00
- SQL> SELECT to_char (add_months (SYSDATE,-1 ),'Yyyy/MM/DD HH24: MI: ss')"1 month before"FROM dual;
- 1 month before
- -------------------
- 06:22:02
- SQL> SELECT to_char (SYSDATE + INTERVAL'-1'MONTH,'Yyyy/MM/DD HH24: MI: ss')"1 month before"FROM dual;
- 1 month before
- -------------------
- 06:22:04
- SQL> SELECT to_char (SYSDATE + INTERVAL'1-1'Year to month,'Yyyy/MM/DD HH24: MI: ss')"1 year and 1 month later"FROM dual;
- 1 year and 1 month later
- ------------------------
- 06:22:06
- SQL> SELECT to_char (SYSDATE + INTERVAL'-1-1'Year to month,'Yyyy/MM/DD HH24: MI: ss')"1 year and 1 month before"FROM dual;
- 1 year and 1 month before
- -------------------------
- 2010/04/20 06:22:08
- SQL>
