常用函数

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’