[MSSQL] 기간 달력 테이블 생성

WITH tmp AS ( 
SELECT idx=1, dt = cast('20090101' AS datetime) 
UNION ALL 
SELECT idx = idx + 1, dt = dateadd(d, 1, dt) 
FROM tmp 
WHERE dt < cast('20101231' AS datetime) 
) 
SELECT 
dt
, year = datepart(year, dt) 
, month = datepart(month, dt) 
, day = datepart(day, dt) 
, weekofyear = datepart(wk , dt)
, weekofmonth = datepart(wk, dt) - datepart(wk, left(convert(varchar, dt, 112), 6)+ '01') + 1
, 요일 = datename(w, dt) 
, 분기 = datepart(q, dt) 
, 반기 = case when datepart(month,dt) BETWEEN 1 AND 6 then '상반기' else '하반기' end 
FROM tmp 
OPTION (maxrecursion 0) 

출처 : http://blog.daum.net/_blog/BlogTypeView.do?blogid=0ILV1&articleno=4211304&_bloghome_menu=recenttext#ajax_history_home