전년도 동 주차의 시작일과 종료일을 각각 8자리 문자로

DECLARE @YYYYMMDD AS VARCHAR(8)='20180308'

DECLARE @PREV_YYYY AS VARCHAR(4) --전년도

DECLARE @WK_NUM AS INT --주차

SELECT @PREV_YYYY = CONVERT(VARCHAR(4),CONVERT(INT,LEFT(@YYYYMMDD, 4)-1))

, @WK_NUM = DATEPART(WK, CONVERT(DATE,@YYYYMMDD))

;

WITH DATE_LIST

AS

(

SELECT CAST (@PREV_YYYY+'-01-01' AS DATETIME) AS DT

UNION ALL

SELECT DT+1

FROM DATE_LIST

WHERE DT+1<=@PREV_YYYY+'-12-31'

)

-- MAXRECURSION : 서버차원의기본값은100입니다. 0을지정하면제한이적용되지않습니다

SELECT CONVERT(VARCHAR(8),MIN(DT),112) AS 전년도시작일자

, CONVERT(VARCHAR(8),MAX(DT),112) AS 전년도종료일자

FROM DATE_LIST

WHERE DATEPART(wk, DT) = @WK_NUM

OPTION(MAXRECURSION 0)