[MSSQL] 주,기간 구하기 week

 select RIGHT('00' + CONVERT(VARCHAR, DATEPART(WEEK, '20131231'), 23), 2)

-----------------------------------------------------------------------------------




 
select *, cast(cast(periodID as varchar) as datetime)
 , DATEPART(wk, cast(cast(periodID as varchar) as datetime))
 ,YearName + cast(DATEPART(wk, cast(periodID as varchar))as varchar)+ '주' as sss
from DimensionPeriod
 
 
--주 기간 구하기
SELECT CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, GETDATE()) - 6, GETDATE())), 101) AS Starting_Sunday,
    CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, GETDATE()), GETDATE())), 101) AS Ending_Saturday
DECLARE @sDate CHAR(8)
 DECLARE @dDate DATETIME
 DECLARE @sStartWeek CHAR(8)
  DECLARE @eEndWeek CHAR(8)
 DECLARE @nWeekOfYear INT
 DECLARE @Starting_Sunday CHAR(8)
 DECLARE @Ending_Saturday CHAR(8)
 SET @sDate = '20110101' -- 여기에 날짜 입력
 SET @dDate = CAST(@sDate AS DATETIME)
 
SELECT @sStartWeek = CONVERT(CHAR(8), @dDate - (DATEPART(dw, @dDate) - 1), 112)
SELECT @nWeekOfYear = DATEPART(wk, @dDate)
select @Starting_Sunday = CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, @sDate) - 6, @sDate)), 101)
select @Ending_Saturday = CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, @sDate), @sDate)), 101)
 
 
SELECT @sStartWeek as 시작날 , @nWeekOfYear as 주차 ,@Starting_Sunday as 주시작, @Ending_Saturday as 주끝날 -- 결과리턴
 
 
SELECT GETDATE(),
--   DATEPART(DW ,'2007-05-01') as 요일, -- DAY OF WEEK'주'에 몇번째인가? 일 월 화 수 목
   DATEPART(WK, '2007-01-01') as 주차 -- WK=WW '월'에 몇번째 주인가?
--   DATEPART(WW ,GETDATE())
--   DATEPART(QQ ,GETDATE()) -- 1/4분기.
from view_DimensionWeekly
 
 
--주 기간 구하기
SELECT CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, GETDATE()) - 6, GETDATE())), 102) AS Starting_Sunday,
    CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, GETDATE()), GETDATE())), 102) AS Ending_Saturday
 
--drop function [getWeekofDay]
--CREATE FUNCTION [dbo].[getWeekofDay] (@Date datetime)  
--RETURNS int AS  
--BEGIN 
--    declare @weeknum int
--    set @weeknum = ceiling((day(@Date)+datepart(dw,left(convert(varchar(10), @Date, 112),6) +'01')-1)/7.0) 
--    return @weeknum
--END
 
--select dbo.getWeekofDay('2008-01-01')  from view_DimensionWeekly
--select regdate, dbo.getWeekofDay(regdate) from table
--select regdate, dbo.getWeekofDay(getdate()) from table
--select regdate, dbo.getWeekofDay('2007-01-01') from table
--select regdate, dbo.getWeekofDay('2007/01/01') from table
--select regdate, dbo.getWeekofDay('2007.01.01') from table
--select regdate, dbo.getWeekofDay('20070101') from table
--select regdate, dbo.getWeekofDay('07-01-01') from table
--select regdate, dbo.getWeekofDay('07/01/01') from table
--select regdate, dbo.getWeekofDay('07.01.01') from table
--select regdate, dbo.getWeekofDay('070101') from table
 
--(regdate 필드는 datetime로 만든 필드입니다.)