MSSQL 날짜 변환표 / 날짜계산

- MSSQL 날짜 변환표 (기준날짜를 대상으로 CONVERT 실행하여 날짜 형변환)
번호	쿼리	결과	코드
0	CONVERT(VARCHAR, GETDATE(), 0)	01 02 2000 1:14PM	MM DD YYYY H:MM
1	CONVERT(VARCHAR, GETDATE(), 1)	01/02/2000	MM/DD/YYYY
2	CONVERT(VARCHAR, GETDATE(), 2)	00.01.02	YY.MM.DD
3	CONVERT(VARCHAR, GETDATE(), 3)	02/01/00	DD/MM/YY
4	CONVERT(VARCHAR, GETDATE(), 4)	02.01.00	DD.MM.YY
5	CONVERT(VARCHAR, GETDATE(), 5)	02-01-00	DD-MM-YY
6	CONVERT(VARCHAR, GETDATE(), 6)	02 01 00	DD MM YY
7	CONVERT(VARCHAR, GETDATE(), 7)	01 02, 00	MM DD, YY
8	CONVERT(VARCHAR, GETDATE(), 8)	13:14:15	HH:MM:SS
9	CONVERT(VARCHAR, GETDATE(), 9)	01 02 2000 1:14:15.678PM	NN DD YYYY H:MM:SS.MS
10	CONVERT(VARCHAR, GETDATE(), 10)	01-02-00	MM-DD-YY
11	CONVERT(VARCHAR, GETDATE(), 11)	02/01/00	DD/MM/YY
12	CONVERT(VARCHAR, GETDATE(), 12)	000102	YYMMDD
13	CONVERT(VARCHAR, GETDATE(), 13)	02 01 2000 13:14:15.678	DD MM YYYY HH:MM:SS.MS
14	CONVERT(VARCHAR, GETDATE(), 14)	13:14:15.678	HH:MM:SS.MS
20	CONVERT(VARCHAR, GETDATE(), 20)	2000-01-02 13:14:15	YYYY-MM-DD HH:MM:SS
21	CONVERT(VARCHAR, GETDATE(), 21)	2000-01-02 13:14:15.678	YYYY-MM-DD HH:MM:SS.MS
22	CONVERT(VARCHAR, GETDATE(), 22)	01/02/00 1:14:15 PM	MM/DD/YY H:M:S
23	CONVERT(VARCHAR, GETDATE(), 23)	2000-01-02	YYYY-MM-DD
24	CONVERT(VARCHAR, GETDATE(), 24)	13:14:15	HH:MM:SS
25	CONVERT(VARCHAR, GETDATE(), 25)	2000-01-02 13:14:15.678	YYYY-MM-DD HH:MM:SS.MS
100	CONVERT(VARCHAR, GETDATE(), 100)	01 02 2000 1:02PM	MM DD YYYY H:MM
101	CONVERT(VARCHAR, GETDATE(), 101)	01/02/2000	MM/DD/YYYY
102	CONVERT(VARCHAR, GETDATE(), 102)	2000.01.02	YYYY.MM.DD
103	CONVERT(VARCHAR, GETDATE(), 103)	02/01/2000	DD/MM/YYYY
104	CONVERT(VARCHAR, GETDATE(), 104)	02/01/2000	DD/MM/YYYY
105	CONVERT(VARCHAR, GETDATE(), 105)	02-01-2000	DD-MM-YYYY
106	CONVERT(VARCHAR, GETDATE(), 106)	02 01 2000	DD MM YYYY
107	CONVERT(VARCHAR, GETDATE(), 107)	01 02, 2000	MM DD, YYYY
108	CONVERT(VARCHAR, GETDATE(), 108)	13:14:15	HH:MM:SS
109	CONVERT(VARCHAR, GETDATE(), 109)	01 02 2000 1:14:15.678PM	MM DD YYYY H:MM:DD.MS
110	CONVERT(VARCHAR, GETDATE(), 110)	01-02-2000	MM-DD-YYYY
111	CONVERT(VARCHAR, GETDATE(), 111)	2000/01/02	YYYY/MM/DD
112	CONVERT(VARCHAR, GETDATE(), 112)	20000102	YYYYMMDD
113	CONVERT(VARCHAR, GETDATE(), 113)	02 01 2000 13:14:15.678	DD MM YYYY HH:MM:DD.MS
114	CONVERT(VARCHAR, GETDATE(), 114)	13:14:15:678	HH:MM:DD:MS
120	CONVERT(VARCHAR, GETDATE(), 120)	2000-01-02 13:14:15	YYYY-MM-DD HH:MM:SS
121	CONVERT(VARCHAR, GETDATE(), 121)	2000-01-02 13:14:15.678	YYYY-MM-DD HH:MM:SS.MS
126	CONVERT(VARCHAR, GETDATE(), 126)	2000-01-02T13:14:15.678	YYYY-MM-DDT HH:MM:SS.MS
127	CONVERT(VARCHAR, GETDATE(), 127)	2000-01-02T13:14:15.678	YYYY-MM-DDT HH:MM:SS.MS
131	CONVERT(VARCHAR, GETDATE(), 131)	1/06/1421 1:13:14:678PM

년 월 일 각각 표기 하기

SELECT DATEPART(yy, getdate()); SELECT DATEPART(mm, getdate()); SELECT DATEPART(dd, getdate());

● DATEADD - 날짜 계산

​

MS-SQL에서 DATEADD() 함수를 사용하여 날짜를 계산(덧셈, 뺄셈)할 수 있습니다.

DATEADD 사용 구문

DATEADD (DATEPART , NUMBER , DATE )

DATEADD 사용 예시

SELECT DATEADD(YY, -1, GETDATE()) AS '1년전' SELECT DATEADD(YY, 1, GETDATE()) AS '1년후' SELECT DATEADD(MM, -1, GETDATE()) AS '1개월전' SELECT DATEADD(MM, 1, GETDATE()) AS '1개월후' SELECT DATEADD(DAY, -10 , GETDATE()) AS '10일전' SELECT DATEADD(DAY, 10 , GETDATE()) AS '10일후'

실행 결과

2019-09-29 23:21:09.833 2021-09-29 23:21:09.833 2020-08-29 23:21:09.833 2020-10-29 23:21:09.833 2020-09-19 23:21:09.833 2020-10-09 23:21:09.833

​

출처 : https://blog.naver.com/dktmrorl/222103311285