MsSql 달력 주차 기준 ISO 8601 (Calendar)

 

ISO 8601 (Calendar) 함수

 ISO 8601 표준 주차 구하는 함수를 만들어보았습니다.

select * from ufn_week_iso('2009') 

실행하시면, 2009년도 주차가 범위별로 조회가 되어집니다.

/****** Object:  UserDefinedFunction [dbo].[ufn_week_seo]    Script Date: 09/20/2011 16:14:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:  서공석
-- Create date: 2011.09.20
-- Description: 달력 주차 기준 ISO 8601 (Calendar) : 삼성주차
-- =============================================
--select * from ufn_week_iso('2009')

create function [dbo].[ufn_week_iso](@p_yyyy  varchar(04))
 
returns @inserttable table
       (yweek     varchar(06),
        sdate             varchar(08),    -- 주차별 시작일자
        edate             varchar(08)     -- 주차별 종료일자
       
       )

BEGIN

declare @temp1 table (
       gbn  varchar(10),
                      weekseq varchar(06),
                      sdate  varchar(08),
                      edate  varchar(08)
                    )
                   
declare @temp2 table (
                      weekseq integer IDENTITY(1,1),
                      sdate  varchar(08),
                      edate  varchar(08)
                    )                   
                                                          

declare @NextYear_FisrstDt  varchar(08),
        @NextYear_FirstDay char(01),
        @Last_Week_From  datetime,
        @Last_Week_To  datetime,
  @CurYear_FisrstDt   varchar(08),
        @CurYear_FirstDay char(01),       
        @First_Week_From datetime,
        @First_Week_To  datetime,       
        @startdate   datetime,
  @enddate   datetime
 
------------------------------------------------------------------------------------------------------------------  
-- 시작주차 계산  START
------------------------------------------------------------------------------------------------------------------
set @CurYear_FisrstDt = convert(varchar(04),convert(integer,@p_yyyy)) + '0101'    
set @CurYear_FirstDay = Datepart(weekday,convert(datetime,@CurYear_FisrstDt))

set @First_Week_From = Case When @CurYear_FirstDay = '1' Then dateadd(dd, -6, @CurYear_FisrstDt)    -- 1월1일이 일요일때
         When @CurYear_FirstDay = '2' then @CurYear_FisrstDt         -- 1월1일이 월요일때
         Else dateadd(dd,(convert(integer,@CurYear_FirstDay) - 2) * -1 ,@CurYear_FisrstDt)
        End
       
set @First_Week_To = Case When @CurYear_FirstDay = '1' Then convert(datetime, @CurYear_FisrstDt)
        else dateadd(dd,(8 - convert(integer,@CurYear_FirstDay) ) ,@CurYear_FisrstDt)
       end       
         
         
if @CurYear_FirstDay = '6' or @CurYear_FirstDay = '7' or @CurYear_FirstDay = '1'   -- 1월 1일이 금, 토, 일 이면 전년도 주차
 begin

    insert into @temp1(gbn, weekseq, sdate, edate)
         values ('startweek','01', convert(varchar(08),dateadd(dd, 1 , @First_Week_To),112),convert(varchar(08),dateadd(dd, 7 , @First_Week_To),112)) 
 end
 
else  
 begin

    insert into @temp1(gbn, weekseq, sdate, edate)
         values ('startweek','01', convert(varchar(08),@First_Week_From,112), convert(varchar(08),@First_Week_To,112)) 
 end
 
------------------------------------------------------------------------------------------------------------------  
-- 시작주차 계산  END
------------------------------------------------------------------------------------------------------------------ 

------------------------------------------------------------------------------------------------------------------  
-- 마지막주차 계산  START
------------------------------------------------------------------------------------------------------------------
-- 다음년도 1월1일
set @NextYear_FisrstDt = convert(varchar(04),convert(integer,@p_yyyy) + 1) + '0101'    
set @NextYear_FirstDay = Datepart(weekday,convert(datetime,@NextYear_FisrstDt))

set @Last_Week_From = Case When @NextYear_FirstDay = '1' Then dateadd(dd, -6, @NextYear_FisrstDt)
         When @NextYear_FirstDay = '2' then @NextYear_FisrstDt
         Else dateadd(dd,(convert(integer,@NextYear_FirstDay) - 2) * -1 ,@NextYear_FisrstDt)
        End
       
set @Last_Week_To = Case When @NextYear_FirstDay = '1' Then convert(datetime,@NextYear_FisrstDt)
        else dateadd(dd,(8 - convert(integer,@NextYear_FirstDay) ) ,@NextYear_FisrstDt)
       end       
         
         
if @NextYear_FirstDay = '6' or @NextYear_FirstDay = '7' or @NextYear_FirstDay = '1'   -- 1월 1일이 금, 토, 일 이면 전년도 주차
 begin

    insert into @temp1(gbn, weekseq, sdate, edate)
         values ('endweek','99', convert(varchar(08),@Last_Week_From,112), convert(varchar(08),@Last_Week_To,112))
 
 end
 
else  
 begin

    insert into @temp1(gbn, weekseq, sdate, edate)
         values ('endweek','99', convert(varchar(08),dateadd(dd,-7,@Last_Week_From),112), convert(varchar(08),dateadd(dd,-7,@Last_Week_To),112))         
        
        
 end
 
------------------------------------------------------------------------------------------------------------------  
-- 마지막주차 계산  END
------------------------------------------------------------------------------------------------------------------ 

select @startdate = sdate from @temp1 where gbn = 'startweek'
select @enddate = edate from @temp1 where gbn = 'endweek'
 
WHILE @startdate <= @enddate
    BEGIN
  
  insert into @temp2(sdate, edate)
    values ( convert(varchar(08),@startdate,112), convert(varchar(08),DATEADD(dd,6,@startdate),112))    
      
  set @startdate = DATEADD(dd,7, @startdate)                       -- 다음주 시작일자 계산
  
    END

insert into @inserttable
select @p_yyyy + right('00' + convert(varchar(02),weekseq),2), sdate, edate from @temp2
--select @p_yyyy + right('00' + convert(varchar(02),weekseq),2), sdate, edate from @temp1

RETURN

END