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 plus integer months. The date argument can be a datetime value or any value that can be implicitly converted to DATE. The integer argument can be an integer or any value that can be implicitly converted to an integer. The return type is always DATE, regardless of the datatype of date. If date is the last day of the month or if the resulting month has fewer days than the day component of date, then the result is the last day of the resulting month. Otherwise, the result has the same day component as date.

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 table employees:

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

Next Month
-----------
07-JUL-1994