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)인 경우
*/