[MSSQL] 년 월 주차 뽑기

SELECT *,DATEPART( YEAR, WK.FRIDAY ) AS [년], DATEPART( MONTH, WK.FRIDAY ) AS [월], ( DATEPART( DAY, WK.THURSDAY ) - 1 ) / 7 + 1 AS [주]
FROM
(
SELECT CONVERT(VARCHAR(10),DATEADD(DAY,-(DATEPART(DW,CONVERT(SMALLDATETIME,'2019-01-01')+(NUMBER * 7) )-2),CONVERT(SMALLDATETIME,'2019-01-01')+(NUMBER * 7)),120) AS MONDAY
,CONVERT(VARCHAR(10),DATEADD(DAY,(6-DATEPART(DW,CONVERT(SMALLDATETIME,'2019-01-01')+(NUMBER * 7))),CONVERT(SMALLDATETIME,'2019-01-01')+(NUMBER * 7)),120) AS FRIDAY
,CONVERT(VARCHAR(10),DATEADD(DAY,(5-DATEPART(DW,CONVERT(SMALLDATETIME,'2019-01-01')+(NUMBER * 7))),CONVERT(SMALLDATETIME,'2019-01-01')+(NUMBER * 7)),120) AS THURSDAY,NUMBER
FROM MASTER.DBO.SPT_VALUES WHERE TYPE ='P' AND NUMBER < 52
) WK

년간 주단위 데이터를 만들일이 있어서 주간 날짜를 뽑아봄 기준은 평일이고 해당 주간의 년/월/주 기준은 목요일로 잡음

출처 : http://www.sqler.com/bSQLQA/384204