mssql 날짜 포맷 모음

2018. 2. 28. 14:28

SELECT CONVERT(CHAR(19), GETDATE(), 0) -- 01 02 2000 1:14PM => MM DD YYYY H:MM

SELECT CONVERT(CHAR(10), GETDATE(), 1) -- 01/02/2000 => MM/DD/YYYY

SELECT CONVERT(CHAR(8), GETDATE(), 2) -- 00.01.02 => YY.MM.DD

SELECT CONVERT(CHAR(8), GETDATE(), 3) -- 02/01/00 => DD/MM/YY

SELECT CONVERT(CHAR(8), GETDATE(), 4) -- 02.01.00 => DD.MM.YY

SELECT CONVERT(CHAR(8), GETDATE(), 5) -- 02-01-00 => DD-MM-YY

SELECT CONVERT(CHAR(8), GETDATE(), 6) -- 02 01 00 => DD MM YY

SELECT CONVERT(CHAR(9), GETDATE(), 7) -- 01 02, 00 => MM DD, YY

SELECT CONVERT(CHAR(8), GETDATE(), 8) -- 13:14:15 => HH:MM:SS

SELECT CONVERT(CHAR(26), GETDATE(), 9) -- 01 02 2000 1:14:15.678PM => MM DD YYYY H:MM:SS.MS

SELECT CONVERT(CHAR(8), GETDATE(), 10) -- 01-02-00 => MM-DD-YY

SELECT CONVERT(CHAR(8), GETDATE(), 11) -- 02/01/00 => DD/MM/YY

SELECT CONVERT(CHAR(6), GETDATE(), 12) -- 000102 => YYMMDD

SELECT CONVERT(CHAR(24), GETDATE(), 13) -- 02 01 2000 13:14:15.678 => DD MM YYYY HH:MM:SS.MS

SELECT CONVERT(CHAR(12), GETDATE(), 14) -- 13:14:15.678 => HH:MM:SS.MS

SELECT CONVERT(CHAR(19), GETDATE(), 20) -- 2000-01-02 13:14:15 => YYYY-MM-DD HH:MM:SS

SELECT CONVERT(CHAR(23), GETDATE(), 21) -- 2000-01-02 13:14:15.678 => YYYY-MM-DD HH:MM:SS.MS

SELECT CONVERT(CHAR(20), GETDATE(), 22) -- 01/02/00 1:14:15 PM => MM/DD/YY H:M:S

SELECT CONVERT(CHAR(10), GETDATE(), 23) -- 2000-01-02 => YYYY-MM-DD

SELECT CONVERT(CHAR(8), GETDATE(), 24) -- 13:14:15 => HH:MM:SS

SELECT CONVERT(CHAR(23), GETDATE(), 25) -- 2000-01-02 13:14:15.678 => YYYY-MM-DD HH:MM:SS.MS

SELECT CONVERT(CHAR(19), GETDATE(), 100) -- 01 02 2000 1:02PM => MM DD YYYY H:MM

SELECT CONVERT(CHAR(10), GETDATE(), 101) -- 01/02/2000 => MM/DD/YYYY

SELECT CONVERT(CHAR(10), GETDATE(), 102) -- 2000.01.02 => YYYY.MM.DD

SELECT CONVERT(CHAR(10), GETDATE(), 103) -- 02/01/2000 => DD/MM/YYYY

SELECT CONVERT(CHAR(10), GETDATE(), 104) -- 02/01/2000 => DD/MM/YYYY

SELECT CONVERT(CHAR(10), GETDATE(), 105) -- 02-01-2000 => DD-MM-YYYY

SELECT CONVERT(CHAR(11), GETDATE(), 106) -- 02 01 2000 => DD MM YYYY

SELECT CONVERT(CHAR(12), GETDATE(), 107) -- 01 02, 2000 => MM DD, YYYY

SELECT CONVERT(CHAR(8), GETDATE(), 108) -- 13:14:15 => HH:MM:SS

SELECT CONVERT(CHAR(26), GETDATE(), 109) -- 01 02 2000 1:14:15.678PM => MM DD YYYY H:MM:DD.MS

SELECT CONVERT(CHAR(10), GETDATE(), 110) -- 01-02-2000 => MM-DD-YYYY

SELECT CONVERT(CHAR(10), GETDATE(), 111) -- 2000/01/02 => YYYY/MM/DD

SELECT CONVERT(CHAR(8), GETDATE(), 112) -- 20000102 => YYYYMMDD

SELECT CONVERT(CHAR(24), GETDATE(), 113) -- 02 01 2000 13:14:15.678 => DD MM YYYY HH:MM:DD.MS

SELECT CONVERT(CHAR(12), GETDATE(), 114) -- 13:14:15:678 => HH:MM:DD:MS

SELECT CONVERT(CHAR(19), GETDATE(), 120) -- 2000-01-02 13:14:15 => YYYY-MM-DD HH:MM:SS

SELECT CONVERT(CHAR(23), GETDATE(), 121) -- 2000-01-02 13:14:15.678 => YYYY-MM-DD HH:MM:SS.MS

SELECT CONVERT(CHAR(23), GETDATE(), 126) -- 2000-01-02T13:14:15.678 => YYYY-MM-DDT HH:MM:SS.MS

SELECT CONVERT(CHAR(23), GETDATE(), 127) -- 2000-01-02T13:14:15.678 => YYYY-MM-DDT HH:MM:SS.MS

-- 위에 있는 내용에 찾는 포맷이 없다면 하기의 쿼리로 대부분 포맷을 대체 할 수 있다.
DECLARE @AAA DATETIME = GETDATE()
SELECT FORMAT(@AAA,'yyyy.MM.dd tt hh:mm')