[MSSQL] 전일, 전주, 전월, 전분기, 전반기, 전년도 구하기

01. 일간

입력값 : 20130228

전일 : SELECT CONVERT(VARCHAR(8), DATEADD(D, -1, CONVERT(DATETIME2, '20130228', 112)), 112)

02. 주간 

입력값 : 20130228

현재주차 : SELECT CONVERT(VARCHAR(4), DATEPART(YYYY, '20130228')) + CONVERT(VARCHAR(2), REPLICATE('0', 2 - LEN(DATEPART(WW, '20130228'))) + CAST(DATEPART(WW, '20130228') AS VARCHAR))

지난주차 : SELECT CONVERT(VARCHAR(4), DATEADD(WW, -1, '20130228'), 112) + CONVERT(VARCHAR(2), REPLICATE('0', 2 - LEN(DATEPART(WW, DATEADD(WW, -1, '20130228')))) + CAST(DATEPART(WW, DATEADD(WW, -1, '20130228')) AS VARCHAR))

03. 월간

입력값 : 201301

지난 월 : SELECT CONVERT(VARCHAR(6), DATEADD(M, -1, '201302' + '01'), 112)

04. 분기

입력값 : 201304

현재 분기 : SELECT CONVERT(VARCHAR(4), DATEPART(YYYY, '201304' + '01')) + CONVERT(VARCHAR(2), REPLICATE('0', 2 - LEN(DATEPART(q, CAST('201304' +  '01' AS DATE)))) + CAST(DATEPART(q, CAST('201304' +  '01' AS DATE)) AS VARCHAR))

 전분기 : SELECT CONVERT(VARCHAR(4), DATEADD(M, -1, '201304' + '01'), 112) + CONVERT(VARCHAR(2), REPLICATE('0', 2 - LEN(DATEPART(q, DATEADD(MONTH, -1, CAST('201304' +  '01' AS DATE))))) + CAST(DATEPART(q, DATEADD(MONTH, -1, CAST('201304' +  '01' AS DATE))) AS VARCHAR))

05. 반기 

입력값 : 201307

현재 반기 :SELECT CONVERT(VARCHAR(4), DATEPART(YYYY, '201307' + '01')) + CASE WHEN DATEPART(MONTH, '201307' + '01') BETWEEN 1 AND 6 THEN '01' ELSE '02' END

지난 반기 : SELECT CONVERT(VARCHAR(4), DATEADD(M, -1, '201307' + '01'), 112) + CASE WHEN DATEPART(MONTH, DATEADD(MONTH, -1, CAST('201307' +  '01' AS DATE))) BETWEEN 1 AND 6 THEN '01' ELSE '02' END

06. 년도

현재년도
SELECT CONVERT(VARCHAR(4), DATEPART(YYYY, '2013' + '0101'))

전년도
SELECT CONVERT(VARCHAR(4), DATEADD(YYYY, -1, '2013' + '0101'), 112)