http://tychejin.blog.me/220764962740
http://www.w3schools.com/sql/func_convert.asp
1. GETDATE()
SELECT GETDATE()
2. YYYY/MM/DD
SELECT CONVERT(VARCHAR(10),GETDATE(),111)
3. YYYYMMDD
SELECT CONVERT(VARCHAR(10),GETDATE(),112)
4. HH:MM:SS
SELECT CONVERT(VARCHAR(8),GETDATE(),108)
5. HHMMSS
SELECT REPLACE(CONVERT(VARCHAR(8),GETDATE(),108),':','')
6. YYYY/MM/DD HH:MM:SS
SELECT REPLACE(CONVERT(VARCHAR(30),GETDATE(),120),'-','/')
7. YYYY/MM/DD HH:MM:SS
SELECT REPLACE(CONVERT(VARCHAR(30),GETDATE(),121),'-','/')
8. YYYY/MM/DD HH:MM:SS
SELECT CONVERT(VARCHAR(10),GETDATE(),111) + SPACE(1) + CONVERT(VARCHAR(8),GETDATE(),108)
9. YYYYMMDDHHMMSS
SELECT CONVERT(VARCHAR(10),GETDATE(),112) + REPLACE(CONVERT(VARCHAR(8),GETDATE(),108),':','')
MSSQL 날짜함수 (GETDATE / CONVERT / DATEPART / DATEADD / DATEDIFF)
http://blog.naver.com/diceworld/220156575978
SELECT GETDATE() AS 날짜
CONVERT([데이터형식], '[변환하고자 하는 데이터]', [스타일])
http://blog.naver.com/diceworld/220156570441
- 기준날짜
|
2000년 01월 02일 13시 14분 15.678초 기준 날짜 형변환 |
- MSSQL 날짜 변환표 (기준날짜를 대상으로 CONVERT 실행하여 날짜 형변환)
|
번호 |
쿼리 |
결과 | 코드 |
|
0 |
CONVERT(CHAR(19), DATETIME, 0) |
01 02 2000 1:14PM | MM DD YYYY H:MM |
|
1 |
CONVERT(CHAR(10), DATETIME, 1) |
01/02/2000 |
MM/DD/YYYY |
|
2 |
CONVERT(CHAR(8), DATETIME, 2) |
00.01.02 |
YY.MM.DD |
|
3 |
CONVERT(CHAR(8), DATETIME, 3) |
02/01/00 |
DD/MM/YY |
|
4 |
CONVERT(CHAR(8), DATETIME, 4) |
02.01.00 |
DD.MM.YY |
|
5 |
CONVERT(CHAR(8), DATETIME, 5) |
02-01-00 |
DD-MM-YY |
|
6 |
CONVERT(CHAR(8), DATETIME, 6) |
02 01 00 |
DD MM YY |
|
7 |
CONVERT(CHAR(9), DATETIME, 7) |
01 02, 00 |
MM DD, YY |
|
8 |
CONVERT(CHAR(8), DATETIME, 8) |
13:14:15 |
HH:MM:SS |
|
9 |
CONVERT(CHAR(26), DATETIME, 9) |
01 02 2000 1:14:15.678PM |
NN DD YYYY H:MM:SS.MS |
|
10 |
CONVERT(CHAR(8), DATETIME, 10) |
01-02-00 |
MM-DD-YY |
|
11 |
CONVERT(CHAR(8), DATETIME, 11) |
02/01/00 |
DD/MM/YY |
|
12 |
CONVERT(CHAR(6), DATETIME, 12) |
000102 |
YYMMDD |
|
13 |
CONVERT(CHAR(24), DATETIME, 13) |
02 01 2000 13:14:15.678 |
DD MM YYYY HH:MM:SS.MS |
|
14 |
CONVERT(CHAR(12), DATETIME, 14) |
13:14:15.678 |
HH:MM:SS.MS |
|
20 |
CONVERT(CHAR(19), DATETIME, 20) |
2000-01-02 13:14:15 |
YYYY-MM-DD HH:MM:SS |
|
21 |
CONVERT(CHAR(23), DATETIME, 21) |
2000-01-02 13:14:15.678 |
YYYY-MM-DD HH:MM:SS.MS |
|
22 |
CONVERT(CHAR(20), DATETIME, 22) |
01/02/00 1:14:15 PM |
MM/DD/YY H:M:S |
|
23 |
CONVERT(CHAR(10), DATETIME, 23) |
2000-01-02 |
YYYY-MM-DD |
|
24 |
CONVERT(CHAR(8), DATETIME, 24) |
13:14:15 |
HH:MM:SS |
|
25 |
CONVERT(CHAR(23), DATETIME, 25) |
2000-01-02 13:14:15.678 |
YYYY-MM-DD HH:MM:SS.MS |
|
100 |
CONVERT(CHAR(19), DATETIME, 100) |
01 02 2000 1:02PM |
MM DD YYYY H:MM |
|
101 |
CONVERT(CHAR(10), DATETIME, 101) |
01/02/2000 |
MM/DD/YYYY |
|
102 |
CONVERT(CHAR(10), DATETIME, 102) |
2000.01.02 |
YYYY.MM.DD |
|
103 |
CONVERT(CHAR(10), DATETIME, 103) |
02/01/2000 |
DD/MM/YYYY |
|
104 |
CONVERT(CHAR(10), DATETIME, 104) |
02/01/2000 |
DD/MM/YYYY |
|
105 |
CONVERT(CHAR(10), DATETIME, 105) |
02-01-2000 |
DD-MM-YYYY |
|
106 |
CONVERT(CHAR(11), DATETIME, 106) |
02 01 2000 |
DD MM YYYY |
|
107 |
CONVERT(CHAR(12), DATETIME, 107) |
01 02, 2000 |
MM DD, YYYY |
|
108 |
CONVERT(CHAR(8), DATETIME, 108) |
13:14:15 |
HH:MM:SS |
|
109 |
CONVERT(CHAR(26), DATETIME, 109) |
01 02 2000 1:14:15.678PM |
MM DD YYYY H:MM:DD.MS |
|
110 |
CONVERT(CHAR(10), DATETIME, 110) |
01-02-2000 |
MM-DD-YYYY |
|
111 |
CONVERT(CHAR(10), DATETIME, 111) |
2000/01/02 |
YYYY/MM/DD |
|
112 |
CONVERT(CHAR(8), DATETIME, 112) |
20000102 |
YYYYMMDD |
|
113 |
CONVERT(CHAR(24), DATETIME, 113) |
02 01 2000 13:14:15.678 |
DD MM YYYY HH:MM:DD.MS |
|
114 |
CONVERT(CHAR(12), DATETIME, 114) |
13:14:15:678 |
HH:MM:DD:MS |
|
120 |
CONVERT(CHAR(19), DATETIME, 120) |
2000-01-02 13:14:15 |
YYYY-MM-DD HH:MM:SS |
|
121 |
CONVERT(CHAR(23), DATETIME, 121) |
2000-01-02 13:14:15.678 |
YYYY-MM-DD HH:MM:SS.MS |
|
126 |
CONVERT(CHAR(23), DATETIME, 126) |
2000-01-02T13:14:15.678 |
YYYY-MM-DDT HH:MM:SS.MS |
|
127 |
CONVERT(CHAR(23), DATETIME, 127) |
2000-01-02T13:14:15.678 |
YYYY-MM-DDT HH:MM:SS.MS |
|
131 |
CONVERT(CHAR(25), DATETIME, 131) |
1/06/1421 1:13:14:678PM |
[참고사항] 자주 쓰이는 변환표
|
번호 |
쿼리 |
결과 | 코드 |
|
21 |
CONVERT(CHAR(23), DATETIME, 21) |
2000-01-02 13:14:15.678 | YYYY-MM-DD HH:MM:SS.MS |
|
23 |
CONVERT(CHAR(10), DATETIME, 23) |
2000-01-02 |
YYYY-MM-DD |
|
112 |
CONVERT(CHAR(8), DATETIME, 112) |
20000102 |
YYYYMMDD |
- 예제 1
|
오늘 날짜를 YYYY-MM-DD 형태로 CONVERT를 이용하여 표시하시오. (2000년 01월 02일 13시 14분 15초 기준)
|
- 예제 1 쿼리
|
SELECT CONVERT(CHAR(10), GETDATE(), 23) AS 날짜
|
|
날짜 | ||
|
2000-01-02 |
- 예제 2
|
오늘 날짜를 YYYY-MM-DD HH:MM:SS.MS 형태로 CONVERT를 이용하여 표시하시오. (2000년 01월 02일 13시 14분 15초 기준)
|
- 예제 2 쿼리
|
SELECT CONVERT(CHAR(23), GETDATE(), 21) AS 날짜
|
|
날짜 | ||
|
2000-01-02 13:14:15.678 |
[참고사항] GETDATE()
|
GETDATE()함수는 오늘날짜를 연, 월, 일, 시, 분, 초, 밀리세컨드까지 갖고 오는 함수입니다.
|
