ORACLE日期函数：取上月末/月初/季末/季初/年末/年初...

Dear 丶 2024-03-16 20:56 25阅读 0赞

#### ORACLE日期函数：取上月末/月初/季末/季初/年末/年初... ####

*  常用函数
 *   *  SYSDATE()
     *  ADD\_MONTHS(date, n)
     *  LAST\_DAY(date)
     *  TRUNC(date)
     *  EXTRACT
     *  ROUND(date)
     *  MONTHS\_BETWEEN(date1, date2)
 *  场景举例
 *   *  取上月末/月初/季末/季初/年末/年初等等数据
     *  输出最近三个月每个月最后一天的数据
     *  删除非近12个月月末的数据
     *  更新每个季度最后7天的数据

## 常用函数 ##

### SYSDATE() ###

返回当前日期和时间。

### ADD\_MONTHS(date, n) ###

返回在给定日期上增加n个月后的日期。

-- 当前月份加3
    SELECT ADD_MONTHS(SYSDATE, 3) FROM DUAL;
    -- 当前月份减3
    SELECT ADD_MONTHS(SYSDATE, -3) FROM DUAL;

### LAST\_DAY(date) ###

返回给定日期所在月份的最后一天的日期。

SELECT LAST_DAY(TO_DATE('2023-10-01', 'YYYY-MM-DD')) FROM DUAL;

这段代码会返回2023年10月31日，即2023年10月的最后一天。

### TRUNC(date) ###

返回给定日期的截断值。

--当前日期所在年份的第一天
    SELECT TRUNC(SYSDATE, 'YEAR') FROM DUAL;
    SELECT TRUNC(SYSDATE, 'Y') FROM DUAL;
    
    --当前日期所在月份的第一天
    SELECT TRUNC(SYSDATE, 'MONTH') FROM DUAL;
    SELECT TRUNC(SYSDATE, 'MM') FROM DUAL;
    
    --当前日期所在季度的第一天
    SELECT TRUNC(SYSDATE, 'Q') FROM DUAL;
    
    --当前日期所在周的第一天
    SELECT TRUNC(SYSDATE, 'IW') FROM DUAL;

### EXTRACT ###

截取日期的各个部分，包括年份、月份、日期等。

--截取当前年份/月份/日期
    SELECT EXTRACT(YEAR FROM SYSDATE) FROM DUAL;
    SELECT EXTRACT(MONTH FROM SYSDATE) FROM DUAL;
    SELECT EXTRACT(DAY FROM SYSDATE) FROM DUAL;
    
    --截取当前日期的季度
    SELECT TRUNC(TO_NUMBER(TO_CHAR(SYSDATE, 'MM')) / 3.1) + 1 AS QUARTER
    FROM DUAL;
    --截取当前时间的小时（24小时制）
    SELECT TO_CHAR(SYSDATE, 'HH24') AS HOUR FROM DUAL;

### ROUND(date) ###

返回给定日期的四舍五入值。  
如果你想计算2022年10月15日14:30:00的四舍五入值，可以使用以下代码：

SELECT ROUND(TO_DATE('2022-10-15 14:30:00', 'YYYY-MM-DD HH24:MI:SS')) FROM DUAL;

这段代码会返回2022年10月16日00:00:00，即将给定日期四舍五入到最近的一天。

### MONTHS\_BETWEEN(date1, date2) ###

返回两个日期之间的月份差。

SELECT 
    MONTHS_BETWEEN(TO_DATE('2022-12-01', 'YYYY-MM-DD'), 
    TO_DATE('2022-10-01', 'YYYY-MM-DD')) FROM DUAL;

## 场景举例 ##

### 取上月末/月初/季末/季初/年末/年初等等数据 ###

--上月末 
    TRUNC(SYSDATE, 'MM') - 1 
    --月末
    ADD_MONTHS(TRUNC(SYSDATE, 'MM'), 1) - 1
    --上月初
    ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -1) 
    --月初
    TRUNC(SYSDATE, 'MM')
    --上季末
    TRUNC(SYSDATE, 'Q') - 1 
    --季末 
    ADD_MONTHS(TRUNC(SYSDATE, 'Q'), 3) - 1
    --上季初
    ADD_MONTHS(TRUNC(SYSDATE, 'Q'), -3) 
    --季初
    TRUNC(SYSDATE, 'Q')
     --上年末
    TRUNC(SYSDATE, 'Y') - 1 
    --年末
    ADD_MONTHS(TRUNC(SYSDATE, 'Y'), 12) - 1
    --上年初
    ADD_MONTHS(TRUNC(SYSDATE, 'Y'), -12) 
    --年初
     TRUNC(SYSDATE, 'Y')

### 输出最近三个月每个月最后一天的数据 ###

在T表中dt为已定义的varchar2类型的当前日期参数，实现筛选出T表中最近三个月每个月最后一天的全部数据

SELECT *
    FROM T
    WHERE TO_DATE(dt, 'YYYY-MM-DD') IN (
        LAST_DAY(ADD_MONTHS(TO_DATE(dt, 'YYYY-MM-DD'), -2)),
        LAST_DAY(ADD_MONTHS(TO_DATE(dt, 'YYYY-MM-DD'), -1)),
        LAST_DAY(TO_DATE(dt, 'YYYY-MM-DD'))
    );

这段代码会在表T中，根据已定义的dt参数（假设它是一个VARCHAR2类型的日期字符串，格式为’YYYY-MM-DD’），筛选出最近三个月每个月最后一天的全部数据，并将结果输出。

我们首先使用 TO\_DATE 函数将 datadate 列从 VARCHAR2 类型转换为 DATE 类型。然后，我们使用 ADD\_MONTHS 和 TRUNC 函数计算当前日期的前三个月，并使用 WHERE 子句筛选出最近三个月的数据。

### 删除非近12个月月末的数据 ###

在T表中有A、B和dt三个字段，dt为已定义的varchar2类型的当前日期参数，实现删除T表中非近12个月月末的数据

DELETE FROM T
    WHERE NOT EXISTS (
        SELECT 1
        FROM (
            SELECT LAST_DAY(ADD_MONTHS(TO_DATE(dt, 'YYYY-MM-DD'), -ROWNUM)) AS MONTH_END
            FROM T
            WHERE ROWNUM <= 12
        )
        WHERE MONTH_END = TO_DATE(dt, 'YYYY-MM-DD')
    );

这段代码会在表T中，根据已定义的dt参数（假设它是一个VARCHAR2类型的日期字符串，格式为’YYYY-MM-DD’），删除非近12个月月末的数据。  
下面这段代码同样可以实现：

DELETE FROM T
    WHERE TO_DATE(dt, 'YYYY-MM-DD') NOT IN (
        SELECT LAST_DAY(ADD_MONTHS(TO_DATE(dt, 'YYYY-MM-DD'), -LEVEL))
        FROM DUAL
        CONNECT BY LEVEL <= 12
    );

### 更新每个季度最后7天的数据 ###

在T表中，dt为已定义的varchar2类型的当前日期参数，实现若dt不为每个季度的最后7天则将‘1’插入到表T中，并且更新表T2中的数据为‘2’

DECLARE
        dt DATE := TO_DATE(:dt, 'YYYY-MM-DD');
        last_day_of_quarter DATE;
    BEGIN
        last_day_of_quarter := ADD_MONTHS(TRUNC(dt, 'Q'), 3) - INTERVAL '1' DAY;
        IF dt < last_day_of_quarter - INTERVAL '6' DAY THEN
            INSERT INTO T VALUES ('1');
            UPDATE T2 SET column_name = '2';
        END IF;
    END;

ADD\_MONTHS(TRUNC(dt, ‘Q’), 3) - INTERVAL ‘1’ DAY是一个表达式，它用于计算给定日期dt所在季度的最后一天。

首先，TRUNC(dt, ‘Q’)会将日期dt截断到所在季度的第一天。例如，如果dt是’2022-05-15’，则TRUNC(dt, ‘Q’)的结果为’2022-04-01’。

然后，使用ADD\_MONTHS函数将截断后的日期加上3个月，得到下一个季度的第一天。例如，如果截断后的日期为’2022-04-01’，则ADD\_MONTHS(TRUNC(dt, ‘Q’), 3)的结果为’2022-07-01’。

最后，从下一个季度的第一天减去1天，得到当前季度的最后一天。例如，如果下一个季度的第一天为’2022-07-01’，则ADD\_MONTHS(TRUNC(dt, ‘Q’), 3) - INTERVAL ‘1’ DAY的结果为’2022-06-30’