728x90
반응형
안녕하세요. E-nan 입니다.
프로그램을 데이터베이스와 연동하여 코딩을 하다보면
문자열이나 날짜/시간을 적절하게 처리해줘야 하는 상황이 자주 발생합니다.
저도 자주 헷갈려서 구글링을 했었는데요.
이번 기회에 블로그에 포스팅 하는겸, 정리해서 올려보겠습니다.
먼저 제 기준으로 자주 사용했던 4가지 쿼리부터 알려드리겠습니다
| 쿼리 | 형태 | 코드 |
| CONVERT(CHAR(23), DATETIME, 21) | 2020-04-17 18:00:00 | YYYY-MM-DD HH:MM:SS |
| CONVERT(CHAR(10), DATETIME, 23) | 2020-04-17 | YYYY-MM-DD |
| CONVERT(CHAR(8), DATETIME, 24) | 18:00:00 | HH:MM:SS |
| CONVERT(CHAR(8), DATETIME, 112) | 20200417 | YYYYMMDD |
위에 4가지 쿼리를 포함하여 전체 변환표입니다.
| 쿼리 | 형태 | 코드 |
| CONVERT(CHAR(19), DATETIME, 0) | 01 02 2000 1:14PM | MM DD YYYY H:MM |
| CONVERT(CHAR(10), DATETIME, 1) | 01/02/2000 | MM/DD/YYYY |
| CONVERT(CHAR(8), DATETIME, 2) | 00.01.02 | YY.MM.DD |
| CONVERT(CHAR(8), DATETIME, 3) | 02/01/00 | DD/MM/YY |
| CONVERT(CHAR(8), DATETIME, 4) | 02.01.00 | DD.MM.YY |
| CONVERT(CHAR(8), DATETIME, 5) | 02-01-00 | DD-MM-YY |
| CONVERT(CHAR(8), DATETIME, 6) | 02 01 00 | DD MM YY |
| CONVERT(CHAR(8), DATETIME, 7) | 01 02, 00 | MM DD, YY |
| CONVERT(CHAR(8), DATETIME, 8) | 13:14:15 | HH:MM:SS |
| CONVERT(CHAR(26), DATETIME, 9) | 01 02 2000 1:14:15.678PM | MM DD YYYY H:MM:SS.MS |
| CONVERT(CHAR(8), DATETIME, 10) | 01-02-00 | MM-DD-YY |
| CONVERT(CHAR(8), DATETIME, 11) | 02/01/00 | DD/MM/YY |
| CONVERT(CHAR(8), DATETIME, 12) | 000102 | YYMMDD |
| CONVERT(CHAR(24), DATETIME, 13) | 02 01 2000 13:14:15.678 | DD MM YYYY HH:MM:SS.MS |
| CONVERT(CHAR(12), DATETIME, 14) | 13:14:15.678 | HH:MM:SS.MS |
| CONVERT(CHAR(19), DATETIME, 20) | 2000-01-02 13:14:15 | YYYY-MM-DD HH:MM:SS |
| CONVERT(CHAR(23), DATETIME, 21) | 2000-01-02 13:14:15.678 | YYYY-MM-DD HH:MM:SS.MS |
| CONVERT(CHAR(20), DATETIME, 22) |
01/02/00 1:14:15 PM | MM/DD/YY H:M:S |
| CONVERT(CHAR(10), DATETIME, 23) | 2020-04-17 | YYYY-MM-DD |
| CONVERT(CHAR(8), DATETIME, 24) | 18:00:00 | HH:MM:SS |
| CONVERT(CHAR(23), DATETIME, 25) |
2000-01-02 13:14:15.678 | YYYY-MM-DD HH:MM:SS.MS |
| CONVERT(CHAR(19), DATETIME, 100) |
01 02 2000 1:02PM | MM DD YYYY H:MM |
| CONVERT(CHAR(10), DATETIME, 101) | 01/02/2000 | MM/DD/YYYY |
| CONVERT(CHAR(10), DATETIME, 102) | 2000.01.02 | YYYY.MM.DD |
| CONVERT(CHAR(10), DATETIME, 103) | 02/01/2000 | DD/MM/YYYY |
| CONVERT(CHAR(10), DATETIME, 104) | 02/01/2000 | DD/MM/YYYY |
| CONVERT(CHAR(10), DATETIME, 105) | 02-01-2000 | DD-MM-YYYY |
| CONVERT(CHAR(11), DATETIME, 106) | 02 01 2000 | DD MM YYYY |
| CONVERT(CHAR(12), DATETIME, 107) | 01 02, 2000 | MM DD, YYYY |
| CONVERT(CHAR(8), DATETIME, 108) | 13:14:15 | HH:MM:SS |
| CONVERT(CHAR(26), DATETIME, 109) |
01 02 2000 1:14:15.678PM | MM DD YYYY H:MM:DD.MS |
| CONVERT(CHAR(10), DATETIME, 110) | 01-02-2000 | MM-DD-YYYY |
| CONVERT(CHAR(10), DATETIME, 111) | 2000/01/02 | YYYY/MM/DD |
| CONVERT(CHAR(8), DATETIME, 112) | 20200417 | YYYYMMDD |
| CONVERT(CHAR(24), DATETIME, 113) |
02 01 2000 13:14:15.678 | DD MM YYYY HH:MM:DD.MS |
| CONVERT(CHAR(12), DATETIME, 114) | 13:14:15:678 | HH:MM:DD:MS |
| CONVERT(CHAR(19), DATETIME, 120) | 2000-01-02 13:14:15 | YYYY-MM-DD HH:MM:SS |
| CONVERT(CHAR(23), DATETIME, 121) | 2000-01-02 13:14:15.678 | YYYY-MM-DD HH:MM:SS.MS |
| CONVERT(CHAR(23), DATETIME, 126) | 2000-01-02T13:14:15.678 | YYYY-MM-DDT HH:MM:SS.MS |
| CONVERT(CHAR(23), DATETIME, 127) | 2000-01-02T13:14:15.678 | YYYY-MM-DDT HH:MM:SS.MS |
| CONVERT(CHAR(25), DATETIME, 131) | 1/06/1421 1:13:14:678PM |
출처 : https://chachahoya.tistory.com/74
공감이나 도움이 되셨다면, 공감버튼을 눌러주세요ㅎㅎ
질문이나 오타, 잘못된 내용 등은 댓글로 남겨주시면 감사하겠습니다 :)
728x90
반응형
댓글