DB2 SQL获取本周一到本周日和上周一到上周日的日期范围

DAYOFWEEK_ISO：周一为1
DAYOFWEEK：周日为1

本周一到本周日：

SELECT
    CURRENT_DATE                                    AS 现在,
    TIMESTAMP(PROD_TIME)                            AS 本周数据,
    CURRENT DATE- (DAYOFWEEK_ISO (CURRENT DATE) -1) AS 本周一,
    CURRENT DATE-(DAYOFWEEK_ISO (CURRENT DATE) -7) 本周日
FROM
    分区.表
WHERE
    TO_CHAR(TIMESTAMP(PROD_TIME),'YYYY-MM-DD') BETWEEN TO_CHAR(CURRENT DATE- (DAYOFWEEK_ISO
    (CURRENT DATE) -1),'yyyy-MM-dd') AND TO_CHAR(CURRENT DATE-(DAYOFWEEK_ISO (CURRENT DATE) -7),
    'yyyy-MM-dd')

结果：

上周一到上周日：周日为1

这里的DAYOFWEEK 传入时间是周日的时候会为当前周

SELECT
    CURRENT_DATE                                     AS 现在,
    TIMESTAMP(PROD_TIME)                             AS 上周数据,
    CURRENT_DATE - (DAYOFWEEK(CURRENT_DATE) + 5) DAY AS 上周一,
    CURRENT_DATE - (DAYOFWEEK(CURRENT_DATE) - 1) DAY AS 上周日
FROM
    分区.表
WHERE
    TIMESTAMP(PROD_TIME) BETWEEN CURRENT_DATE - (DAYOFWEEK(CURRENT_DATE) + 5) DAY AND CURRENT_DATE
    - (DAYOFWEEK(CURRENT_DATE) - 1) DAY

结果：

上周一到上周日：周一为1

这里的DAYOFWEEK_ISO 传入时间是周日的时候会为上周

SELECT
    CURRENT_DATE                                         AS 现在,
    TIMESTAMP(PROD_TIME)                                 AS 上周数据,
    CURRENT_DATE - (DAYOFWEEK_ISO(CURRENT_DATE) + 6) DAY AS 上周一,
    CURRENT_DATE - (DAYOFWEEK_ISO(CURRENT_DATE) ) DAY    AS 上周日
FROM
    分区.表
WHERE
    TIMESTAMP(PROD_TIME) BETWEEN CURRENT_DATE - (DAYOFWEEK_ISO(CURRENT_DATE) + 6) DAY AND
    CURRENT_DATE - (DAYOFWEEK_ISO(CURRENT_DATE) ) DAY