[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), 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)