DB2 上一个季月 SQL

最新推荐文章于 2022-12-04 19:03:53 发布

最新推荐文章于 2022-12-04 19:03:53 发布 · 214 阅读

本文介绍了一个 DB2 自定义函数 QTR 的实现细节，该函数接收日期参数并返回对应的上一季度末月份字符串，例如 '2022-12'。通过 CASE WHEN 语句判断季度并相应调整年份和月份。

CREATE FUNCTION "DB2INST1"."QTR"
("DDATE" DATE
)
RETURNS CHARACTER(7)
SPECIFIC "DB2INST1"."SQL130225092927300"
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
STATIC DISPATCH
CALLED ON NULL INPUT
EXTERNAL ACTION
INHERIT SPECIAL REGISTERS
BEGIN ATOMIC
DECLARE v CHARACTER(7) default '';

    --declare cursor1 cursor for
    set v= (
      select
          CASE
            WHEN TO_CHAR(DDATE,'Q') ='1'
              THEN to_char(year(DDATE)-1)||'-12'
            when TO_CHAR(DDATE,'Q') ='2'
              THEN to_char(year(DDATE))||'-03'
            when TO_CHAR(DDATE,'Q') ='3'
              THEN to_char(year(DDATE))||'-06'
            when TO_CHAR(DDATE,'Q') ='4'
              THEN to_char(year(DDATE))||'-09'
          END
        --into :v     -- 上个季月能够知道跨年
        from sysibm.sysdummy1
        fetch first 1 rows only ) ;

    --fetch first 1 rows only
    --set sum = a + b;
    return v;
END;