개발 참고자료

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))+'번째날짜입니다.';




--YYYY/MM/DD
Select Convert(varchar(10),Getdate(),111)


--YYYYMMDD
Select Convert(varchar(8),Getdate(),112)


--HH:MM:SS
Select Convert(varchar(8),Getdate(),108)


--HH:MM:SS:mmm
Select Convert(varchar(12),Getdate(),114)


--HHMMSS
Select Replace(Convert(varchar(8),Getdate(),108),':','')


--HHMMSSmmm
Select Replace(Convert(varchar(12),Getdate(),114),':','')


--YYYY/MM/DD HH:MM:SS
Select Replace(Convert(varchar(30),Getdate(),120),'-','/')


--YYYY/MM/DD HH:MM:SS
Select Replace(Convert(varchar(30),Getdate(),121),'-','/')


--YYYY/MM/DD HH:MM:SS
Select Convert(varchar(10),Getdate(),111) + Space(1) + Convert(varchar(8),Getdate(),108)


--YYYYMMDDHHMMSS
Select Convert(varchar(10),Getdate(),112) + Replace(Convert(varchar(8),Getdate(),108),':','')




/* 문자를 날짜로 */


SELECT CONVERT (DATE, '2014-12-16')
, CONVERT (DATE, '2014/12/16')
, CONVERT (DATE, '20141216')
, CONVERT (DATETIME, '2014-12-16')
, CONVERT (DATETIME, '2014-12-16 15:10:01')




/* 숫자를 문자로 */


CONVERT(데이터 타입, 데이터)


CONVERT(varchar(10), 데이터값)