IF EXISTS (SELECT name
FROM sysobjects
WHERE name = 'calendar_SELECT_sp'
AND type = 'P')
DROP PROCEDURE calendar_SELECT_sp
GO
create proc calendar_SELECT_sp
@i_today char(8) = ''
as
--0단계: 전처리작업(변수 선언 및 정의)
declare @Caledar Table(
tmpDate char(8),
tmpWeekday char(2)
)
--반복제어변수,총 일수,시작일자,마지막일자 순으로 선언
DECLARE @intLoop int,
@term int,
@f_date char(8),
@l_date char(8)
--반복제어변수 초기값 정의
Set @intLoop = 1
--1단계: 이번달에서 처음날짜 /마지막날짜를 설정
if @i_today = ''
Select @f_date = left(CONVERT(VARCHAR(8),DATEADD(DAY, 1, getdate()), 112),6) + '01',
@l_date = left(CONVERT(VARCHAR(8), DATEADD(Month, 1, getdate()), 112),6) + '01'
else
Select @f_date = left(CONVERT(VARCHAR(8),DATEADD(DAY, 1, @i_today), 112),6) + '01',
@l_date = left(CONVERT(VARCHAR(8), DATEADD(Month, 1, @i_today), 112),6) + '01'
--2단계: 반복제어변수(총 일수)
Select @term = DATEDIFF(DAY, @f_date, @l_date)
--3단계: 처음날짜 추가(전처리작업: 20030600이라는 일자는 없으므로)
insert into @Caledar(tmpDate, tmpWeekday)
Select @f_date,
Case DatePart(weekday, CONVERT(VARCHAR(8),@f_date, 112))
When 1 then '일'
When 2 then '월'
When 3 then '화'
When 4 then '수'
When 5 then '목'
When 6 then '금'
When 7 then '토'
End
--4단계: 총 일수까지 Loop
while @intLoop < @term
begin
--달력 데이터 추가
insert into @Caledar(tmpDate, tmpWeekday)
Select CONVERT(VARCHAR(8),DATEADD(DAY, @intLoop, @f_date), 112),
Case DatePart(weekday,
CONVERT(VARCHAR(8),DATEADD(DAY, @intLoop, @f_date), 112))
When 1 then '일'
When 2 then '월'
When 3 then '화'
When 4 then '수'
When 5 then '목'
When 6 then '금'
When 7 then '토'
End
--반복제어변수 =+1
Select @intLoop = @intLoop + 1
end
--Output: 달력생성 결과
Select * from @Caledar
calendar_SELECT_sp
