ms-sql 해당주의 시작일과 종료일 구하는 방법

DECLARE @old_datefirst INT

SELECT @old_datefirst = @@DATEFIRST

DECLARE @v_dayname_monday INT

SELECT @v_dayname_monday = 1

SET DATEFIRST @v_dayname_monday

DECLARE @v_date DATETIME

SELECT @v_date = '2009-08-01'    ---특정 날짜 지정.

SELECT @v_date date

, DATEPART(year, @v_date) [Year]

, DATEPART(week, @v_date) [Week of Year]

, DATEPART(dayofyear, @v_date) [Day of Year]

, CASE

WHEN DATEPART(weekday, @v_date) > 1 THEN DATEADD(day, (DATEPART(weekday, @v_date)-1) * -1 , @v_date)

ELSE @v_date END [First-day of Week]

, CASE

WHEN DATEPART(weekday, @v_date) < 7 THEN DATEADD(day, 7-DATEPART(weekday, @v_date), @v_date)

ELSE @v_date END [Last-day of Week]

SET DATEFIRST @old_datefirst

/*

실행결과

date                 Year Week of Year Day of Year First-day of Week   Last-day of Week

2009-08-01 2009 31                         213                 2009-07-27            2009-08-02

*/


- 날짜 변환 - 

의미	PARAMETER	QUERY문
mon dd yyyy hh:mmAM (or PM)	100	select convert(char, getdate(), 100)
mm/dd/yyyy	101	select convert(char, getdate(), 101)
yyyy.mm.dd	102	select convert(char, getdate(), 102)
dd/mm/yyyy	103	select convert(char, getdate(), 103)
dd.mm.yyyy	104	select convert(char, getdate(), 104)
dd-mm-yyyy	105	select convert(char, getdate(), 105)
dd mon yyyy	106	select convert(char, getdate(), 106)
mon dd, yyyy	107	select convert(char, getdate(), 107)
hh:mm:ss	108	select convert(char, getdate(), 108)
mon dd yyyy hh:mm:ss:mmmAM (or PM)	109	select convert(char, getdate(), 109)
mm-dd-yyyy	110	select convert(char, getdate(), 110)
yyyy/mm/dd	111	select convert(char, getdate(), 111)
yyyymmdd	112	select convert(char, getdate(), 112)
dd mon yyyy hh:mm:ss:mmm	113	select convert(char, getdate(), 113)
hh:mm:ss:mmm(24h)	114	select convert(char, getdate(), 114)
yyyy-mm-dd hh:mm:ss(24h)	120	select convert(char, getdate(), 120)
yyyy-mm-dd hh:mm:ss.mmm	121	select convert(char, getdate(), 121)

대표적으로 많이 쓰는 얘들이 위와 같다.


/*
-- 해당 주의 시작일과 종료일 구하는 방법
*/
DECLARE @NOWDATE DATETIME
SELECT @NOWDATE = CONVERT(VARCHAR, GETDATE(), 23) --GETDATE()
--지난주를 구하는 경우
--SELECT @NOWDATE = CONVERT(VARCHAR, DATEADD(WW, -1,GETDATE()), 23)

DECLARE @WYEAR INT -- 해당 년도
DECLARE @WWEEK INT -- 해당 주차
DECLARE @SWEEK DATETIME -- 해당주 시작일
DECLARE @EWEEK DATETIME -- 해당주 종료일

SELECT
  @WYEAR = DATEPART(year, @NOWDATE)
 , @WWEEK = DATEPART(week, @NOWDATE)
 , @SWEEK =
  (CASE
   WHEN DATEPART(weekday, @NOWDATE) > 1 THEN DATEADD(day, (DATEPART(weekday, @NOWDATE)) * -1 , @NOWDATE)
   ELSE @NOWDATE END
  )
 , @EWEEK =
  (CASE
   WHEN DATEPART(weekday, @NOWDATE) < 7 THEN DATEADD(day, 7-DATEPART(weekday, @NOWDATE), @NOWDATE)
   ELSE @NOWDATE END
  )

select @NOWDATE Date , @WYEAR Year, @WWEEK Week, @SWEEK Week_Start, @EWEEK Week_End

/*
-- varchar(14)인 경우
DECLARE @VNOWDATE VARCHAR(14)
DECLARE @VSWEEK VARCHAR(14)
DECLARE @VEWEEK VARCHAR(14)
SET @VNOWDATE = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, @NOWDATE, 20),'-',''),':',''),' ','')
SET @VSWEEK = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, @SWEEK, 20),'-',''),':',''),' ','')
SET @VEWEEK = REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR, @EWEEK, 20),'-',''),':',''),' ','')
SELECT @VNOWDATE Date, @WYEAR Year, @WWEEK Week, @VSWEEK Week_Start, @VEWEEK Week_End
-- //varchar(14)인 경우
*/