DB2.Funciton - 해당월에_마지막일자_구하기

[ DB2.Funciton - 해당월에_마지막일자_구하기 ]

 

* 해당일자에 마지막 일자를 반환됩니다 *

SET SCHEMA MISADM;

 

SET CURRENT PATH = "SYSIBM","SYSFUN","SYSPROC","MISADM";

 

CREATE FUNCTION COM.GET_LASTDAY

 (ASDATE VARCHAR(8)

 )

  RETURNS CHARACTER(8)

  SPECIFIC COM.GET_LASTDAY

  LANGUAGE SQL

  DETERMINISTIC

  READS SQL DATA

  STATIC DISPATCH

  CALLED ON NULL INPUT

  EXTERNAL ACTION

  INHERIT SPECIAL REGISTERS

  BEGIN ATOMIC

    DECLARE chkDate integer ;

    DECLARE vReturn CHARACTER(8);

    IF ASDATE IS NULL THEN

      RETURN '';

    END IF;

    SET chkDate = (

      SELECT LENGTH(REPLACE(ASDATE, '-',''))

        FROM COM.Z_COMCDCOD_TBL

        FETCH FIRST 1 ROWS ONLY ) ;

    IF CHKDATE < 8 THEN

      SET vReturn = '' ;

    ELSE

      SET vReturn = (

        SELECT SUBSTR(REPLACE(CHAR(DATE(SUBSTR(asDate,1,4)||'-'|| SUBSTR(

            asDate,5,2) ||'-'|| SUBSTR(asDate,7,2)) + 1 MONTHS - DAY(DATE(

            SUBSTR(asDate,1,4)||'-'|| SUBSTR(asDate,5,2) ||'-'|| SUBSTR(asDate

            ,7,2)) + 1 MONTHS) DAYS) ,'-',''),1,8)

          FROM COM.Z_COMCDCOD_TBL

          FETCH FIRST 1 ROWS ONLY ) ;

    END IF ;

    IF vReturn IS NULL THEN

      SET vReturn = '';

    END IF;

    RETURN vReturn;

  END;

 

COMMENT ON FUNCTION COM.GET_LASTDAY

 (VARCHAR(8)

 )

  IS '해당일자에 마지막 일자를 반환됩니다.';