Oracle ADD_MONTHS 使用

需要脚本，自动清空分区数据。表是按月分区，希望每月自动执行一次，清空下个月的分区数据。

 

分区后缀是两位数月份，不足前面补0。

 

SQL> select lpad(extract(month from(add_months(to_date('2012-12-22 00:00:00', 'yyyy-mm-dd hh24:mi:ss'), 1))), 2, '0') aa from dual;
 
AA
--
01
 
SQL> select lpad(extract(month from(add_months(to_date('2012-11-22 00:00:00', 'yyyy-mm-dd hh24:mi:ss'), 1))), 2, '0') aa from dual;
 
AA
--
12
 
SQL>

标准文档：

ADD_MONTHS

Syntax

Description of the illustration add_months.gif

Purpose

ADD_MONTHS returns the date date plusinteger months. The date argument can be a datetime value or any value that can be implicitly converted toDATE. The integer argument can be an integer or any value that can be implicitly converted to an integer. The return type is alwaysDATE, regardless of the datatype of date. Ifdate is the last day of the month or if the resulting month has fewer days than the day component ofdate, then the result is the last day of the resulting month. Otherwise, the result has the same day component asdate.

See Also:

Table 2-10, "Implicit Type Conversion Matrix" for more information on implicit conversion

Examples

The following example returns the month after the hire_date in the sample tableemployees:

SELECT TO_CHAR(     ADD_MONTHS(hire_date,1),     'DD-MON-YYYY') "Next month"     FROM employees      WHERE last_name = 'Baer';Next Month-----------07-JUL-1994