[Mssql] 연도별 주차 SQL문

연도별로 주차 구하는 SQL문 입니다. (* 월요일 기준)

 

SELECT '2020' AS YEAR
	,CASE WHEN LEN(NUMBER + 1) = 1 THEN '0' + CAST(NUMBER + 1 AS VARCHAR) ELSE CAST(NUMBER + 1 AS VARCHAR) END AS WEEK
	,'Week ' + CASE WHEN LEN(NUMBER + 1) = 1 THEN '0' + CAST(NUMBER + 1 AS VARCHAR) ELSE CAST(NUMBER + 1 AS VARCHAR) END AS WEEK_NM
	,CONVERT(VARCHAR(10), DATEADD(DAY, - (DATEPART(DW, CONVERT(SMALLDATETIME, '2020' + '-01-01') + (NUMBER * 7)) - 2), CONVERT(SMALLDATETIME, '2020'+'-01-01') + (NUMBER * 7)), 120) AS FROM_DATE
	,CONVERT(VARCHAR(10), DATEADD(DAY, - (DATEPART(DW, CONVERT(SMALLDATETIME, '2020' + '-01-01') + (NUMBER * 7)) - 2), CONVERT(SMALLDATETIME, '2020'+'-01-01') + (NUMBER * 7)+6), 120) AS TO_DATE
FROM MASTER.DBO.SPT_VALUES
WHERE TYPE = 'P'
	AND NUMBER < 52 ;

 

결과