know-how? know-way!!

달력 쿼리 : calender.sql

Declare @Year int
Set @Year = '2013'

Declare @month int, @day int
Set @month = 1
Set @day = 1

Declare @Calender table
(
 YYYYMMDD  varchar(8) Primary Key
 , NO_Year  int
 , NO_Month  int
 , NO_Day  int
 , NM_Year  varchar(4)
 , NM_Month  varchar(2)
 , NM_Day  varchar(2)
 , NO_WeekDay int
 , NM_WeekDay varchar(20)
 , NO_DayCount int
)

While @month <= 12 Begin
 While @day <= 31 Begin
  If @Year % 4 = 0 And @month = 2 And @day = 30 Begin Break End
  Else If @month = 2 And @day = 29 Begin Break End
  Else If (@month = 4 or @month = 6 or @month = 9 or @month = 11) And @day = 31 Begin Break End
  
  Insert Into @Calender (YYYYMMDD
       , NO_Year, NO_Month, NO_Day
       , NM_Year, NM_Month, NM_Day
       , NO_WeekDay
       , NM_WeekDay
       , NO_DayCount)
  Values (
   Convert(varchar, @Year) + Right('0'+Convert(varchar, @month),2) + Right('0'+Convert(varchar, @day),2)
   , @Year, @month, @day
   , Convert(varchar, @Year), Right('0'+Convert(varchar, @month),2), Right('0'+Convert(varchar, @day),2)
   , DatePart(dw, Convert(varchar, @Year) + Right('0'+Convert(varchar, @month),2) + Right('0'+Convert(varchar, @day),2))   
   , DateName(dw, Convert(varchar, @Year) + Right('0'+Convert(varchar, @month),2) + Right('0'+Convert(varchar, @day),2))
   , DatePart(dy, Convert(varchar, @Year) + Right('0'+Convert(varchar, @month),2) + Right('0'+Convert(varchar, @day),2))
  )
  
  Set @day = @day + 1
 End
 
 Set @day = 1
 Set @month = @month + 1
 
End
  
Select * From @Calender