DB2 날짜 구하기, 주차 구하기(재귀함수 recursive function 이용)

DB2 월별 날짜(일수) 구하기, 주차 구하기(재귀함수 recursive function 이용)

1. 월별 날짜 구하는 방법, 월별 일수 구하는 방법

	
 
WITH DATE_RANGE(DT, WEEK) AS
(
    SELECT 
          DATE('2011-01-01') AS DT
         ,WEEK_ISO('2011-01-01') AS WEEK
    FROM SYSIBM.SYSDUMMY1
 
 UNION ALL
 
 SELECT 
       DATE(DT) + 1 DAY AS DT
      ,WEEK_ISO(DATE(DT) + 1 DAY) AS WEEK
 FROM DATE_RANGE
 WHERE DT < DATE('2011-12-31')
)
SELECT 
      DT  ,WEEK
FROM DATE_RANGE 
	

2. 월별 주차 구하는 방법

	
 
WITH DATE_RANGE(DT, WEEK) AS
(
    SELECT 
     CHAR(DATE('2011' ||'-01-01'), ISO) AS DT
     ,WEEK_ISO(CHAR(DATE('2011' || '-01-01'), ISO)) AS WEEK
    FROM SYSIBM.SYSDUMMY1
 
 UNION ALL
 
 SELECT 
     CHAR(DATE(DT) + 1 DAY, ISO) AS DT
     ,WEEK_ISO(CHAR(DATE(DT) + 1 DAY, ISO)) AS WEEK
 FROM DATE_RANGE
 WHERE DT < CHAR(DATE('2011' || '-12-31'), ISO)
)
SELECT 
     DECIMAL(SUBSTR(CHAR(DT),6,2)) AS MON
 ,WEEK
FROM DATE_RANGE
GROUP BY DECIMAL(SUBSTR(CHAR(DT),6,2)), WEEK 
	


DATE('2011-01-01') 이 ISO 기준으로 바뀌지 않으면 ERROR 나는 경우가 있음

내용 참조

http://publib.boulder.ibm.com/infocenter/iadthelp/v7r0/index.jsp?topic=/com.ibm.etools.iseries.langref2.doc/rbafzmst283.htm