SQL | DB2和MySQL取近几个季度末和近几个月末时间

数据库取近几个季度末和月末时间，以用DB2和MySQL取近4个季度末和近12个月末为例。

取近4个季度末

DB2语句

WITH T (STARTDATE) AS
(
VALUES CURRENT_DATE - (DAY(CURRENT_DATE)-1) DAY - (MOD(MONTH(CURRENT_DATE)-1,3)) MONTH
UNION ALL
SELECT STARTDATE - 3 MONTH
FROM T
WHERE CURRENT_DATE <= STARTDATE - 3 MONTH + 1 YEAR
)
SELECT STARTDATE - 1 DAY ENDDATE_DB2
FROM T
ORDER BY STARTDATE DESC

MySQL 语句

WITH RECURSIVE T (STARTDATE) AS 
(SELECT
    CURRENT_DATE() - INTERVAL (DAY(CURRENT_DATE()) - 1) DAY - INTERVAL (MOD(MONTH(CURRENT_DATE())-1,3)) MONTH
    UNION ALL
    SELECT
        STARTDATE - INTERVAL 3 MONTH
    FROM
       T
    WHERE CURRENT_DATE() < STARTDATE - INTERVAL 3 MONTH + INTERVAL 1 YEAR
    )
    SELECT
        STARTDATE - INTERVAL 1 DAY QUENDDATE
    FROM T
ORDER BY STARTDATE DESC

取近12个月末

 DB2语句

WITH T (STARTDATE) AS
(
VALUES CURRENT_DATE - (DAY(CURRENT_DATE) - 1) DAY
UNION ALL
SELECT STARTDATE - 1 MONTH
FROM T
WHERE CURRENT_DATE <= STARTDATE + 11 MONTH
)
SELECT STARTDATE - 1 DAY MONTHEND_DB2
FROM T
ORDER BY STARTDATE DESC

 MySQL语句

WITH RECURSIVE T (STARTDATE) AS 
(SELECT
    CURRENT_DATE() - INTERVAL (DAY(CURRENT_DATE()) - 1) DAY
    UNION ALL
    SELECT
        STARTDATE - INTERVAL 1 MONTH
    FROM 
       T
    WHERE CURRENT_DATE() < STARTDATE - INTERVAL 1 MONTH + INTERVAL 1 YEAR
    )
    SELECT
        STARTDATE - INTERVAL 1 DAY MONTHEND_MYSQL
    FROM T
ORDER BY STARTDATE DESC

 

如有不足之处，还请各位大神指正~