ORACLE/PLSQL: ADD_MONTHS FUNCTION

Requirement
If we would like to do month calculation in SQL, how to do it?

Solution
Oracle provided a build-in ADD_MONTHs function for us, the syntax is as bellow:

For example, if I would like to get last month and in Chinese calendar system format, the SQL statement will be looking like this:

SELECT TRIM(TO_CHAR(EXTRACT(YEAR
                            FROM ADD_MONTHS(SYSDATE, -1))-1911, '000'))|| TRIM(TO_CHAR(EXTRACT(MONTH
                                                                                             FROM ADD_MONTHS(SYSDATE, -1)), '00')) AS LAST_MONTH
FROM DUAL

Reference
[1] http://www.techonthenet.com/oracle/functions/add_months.php
[2] http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions004.htm