[MSSQL]CONVERT, DATEADD, GETDATE, DAATEPART 이용해서 날짜구하기

SELECT GETDATE(); --오늘날짜

SELECT CONVERT(CHAR(8), DATEADD(dd,-1,GETDATE()),112); --어제날짜

SELECT CONVERT(CHAR(8), DATEADD(dd,+1,GETDATE()),112); --내일날짜

SELECT CONVERT(CHAR(8), DATEADD(mm,+3, GETDATE()),112); -- 3달후날짜

SELECT CONVERT(CHAR(8), DATEADD(yy,+1, GETDATE()),112); -- 1년후오늘날짜

SELECT CONVERT(CHAR(8), DATEADD(mm, -1, GETDATE()-DAY(GETDATE()-1)),112); --지난달1일

SELECT CONVERT(CHAR(8), DATEADD(mm, 0, GETDATE()-DAY(GETDATE())),112); --지난달말일

SELECT CONVERT(CHAR(8), DATEADD(dd, 0, GETDATE()-DAY(GETDATE()-1)),112); --이번달1일

SELECT CONVERT(CHAR(8), DATEADD(mm, +1, GETDATE()-DAY(GETDATE())),112); --이번달말일

SELECT CONVERT(CHAR(8), DATEADD(MM, +1, GETDATE()-DAY(GETDATE()-1)),112); --다음달1일

SELECT CONVERT(CHAR(8), DATEADD(MM, +2, GETDATE()-DAY(GETDATE())),112); --다음달말일

SELECT CONVERT(CHAR(8), DATEADD(MM, -2, GETDATE()-DAY(GETDATE()-1)),112); -- 2달전1일

SELECT CONVERT(CHAR(8), DATEADD(MM, -1, GETDATE()-DAY(GETDATE())),112); --2달전말일

SELECT CONVERT(CHAR(8), DATEADD(YY, 0, GETDATE()-DATEPART(DAYOFYEAR, GETDATE()-1)),112); --올해1월1일

SELECT CONVERT(CHAR(8), DATEADD(YY, +1, GETDATE()-DATEPART(DAYOFYEAR, GETDATE())),112); --올해12월말일

SELECT CONVERT(CHAR(8), DATEADD(YY, +1, GETDATE()-DATEPART(DAYOFYEAR, GETDATE()-1)),112); --내년1월1일

SELECT CONVERT(CHAR(8), DATEADD(YY, +2, GETDATE()-DATEPART(DAYOFYEAR, GETDATE())),112); --내년12월말일

SELECT DATEPART(DAYOFYEAR,'2010-07-20'); -- 2010-01-01 ~ 2010-07-02의날수

SELECT DATEPART(WEEK, '2010-07-20');

SELECT DATEPART(WEEKDAY, '2010-07-20');

 

DECLARE @WEEK_DAY DATETIME;

SET @WEEK_DAY = '2010-08-15';

SELECT

CASE DATEPART(WEEKDAY, @WEEK_DAY) WHEN '1' THEN '일요일'

  WHEN '2' THEN '월요일'

  WHEN '3' THEN '화요일'

  WHEN '4' THEN '수요일'

  WHEN '5' THEN '목요일'

  WHEN '6' THEN '금요일'

  ELSE '토요일'

END AS '요일';

 

SELECT DATEPART(DAYOFYEAR, '2010-08-15');

SELECT DATEPART(WEEK, '2010-08-15');

 

 

DECLARE @_date CHAR(8);

SET @_date = REPLACE('2010-07-20','-','');

SELECT SUBSTRING(@_date,1,4) + '년' + substring(@_date,5,2) +'월' + substring(@_date,7,2) + '일입니다'

,CONVERT(VARCHAR, DATEPART(WEEK, @_date))+'번째주입니다'

,CONVERT(VARCHAR, DATEPART(dayofyear, @_date))+'번째날짜입니다.';

 

 

DECLARE @_date DATETIME;

SET @_date = CONVERT(CHAR(8), REPLACE('2010-07-20','-',''),112);

SELECT CONVERT(CHAR(4),DATEPART(YEAR,@_date)) + '년' ,CONVERT(CHAR(2),DATEPART(MONTH,@_date))+'월', CONVERT(CHAR(2),DATEPART(DAY,@_date))+'일';

SELECT CONVERT(VARCHAR, DATEPART(WEEK, @_date))+'번째주입니다'

,CONVERT(VARCHAR, DATEPART(dayofyear, @_date))+'번째날짜입니다.';

참조 - http://dramatic.tistory.com/13