본문 바로가기

MSSQL

[MSSQL] 날짜 형식 변환(CONVERT, DATETIME)

반응형

※ [참고사항] 자주 사용되는 코드는 Red Font

Query 코드 결과
CONVERT(CHAR(19), DATETIME, 0) MMM DD YYYY H:MM Dec  7 2021 12:47PM       
CONVERT(CHAR(10), DATETIME, 1) MM/DD/YYYY 12/07/21                  
CONVERT(CHAR(8), DATETIME, 2) YY.MM.DD 21.12.07                  
CONVERT(CHAR(8), DATETIME, 3) DD/MM/YY 07/12/21                  
CONVERT(CHAR(8), DATETIME, 4) DD.MM.YY 07.12.21                  
CONVERT(CHAR(8), DATETIME, 5) DD-MMM-YY 07 Dec 2            
CONVERT(CHAR(8), DATETIME, 6) DD MMM YY Dec 07, 2                 
CONVERT(CHAR(9), DATETIME, 7) MM DD, YY 12 01, 21
CONVERT(CHAR(8), DATETIME, 8) HH:MM:SS 12:47:32                  
CONVERT(CHAR(26), DATETIME, 9) MMM DD YYYY H:MM:SS.MS Dec  7 2021 12:47:32:360PM
CONVERT(CHAR(8), DATETIME, 10) MM-DD-YY 12-07-21                  
CONVERT(CHAR(8), DATETIME, 11) DD/MM/YY 21/12/07                  
CONVERT(CHAR(6), DATETIME, 12) YYMMDD 211207                    
CONVERT(CHAR(24), DATETIME, 13) DD MMM YYYY HH:MM:SS.MS 07 Dec 2021 12:47:32:360
CONVERT(CHAR(12), DATETIME, 14) HH:MM:SS.MS 12:47:32:360             
CONVERT(CHAR(19), DATETIME, 20) YYYY-MM-DD HH:MM:SS 2021-12-07 12:47:32       
CONVERT(CHAR(23), DATETIME, 21) YYYY-MM-DD HH:MM:SS.MS 2021-12-07 12:47:32.360   
CONVERT(CHAR(20), DATETIME, 22) MM/DD/YY H:M:S 12/07/21 12:47:32 PM      
CONVERT(CHAR(10), DATETIME, 23) YYYY-MM-DD 2021-12-07                
CONVERT(CHAR(8), DATETIME, 24)  HH:MM:SS 12:47:32                  
CONVERT(CHAR(23), DATETIME, 25)  YYYY-MM-DD HH:MM:SS.MS 2021-12-07 12:47:32.360   
CONVERT(CHAR(19), DATETIME, 100)   MMM DD YYYY H:MM Dec  7 2021 12:47PM       
CONVERT(CHAR(10), DATETIME, 101) MM/DD/YYYY 12/07/2021                
CONVERT(CHAR(10), DATETIME, 102)  YYYY.MM.DD 2021.12.07                
CONVERT(CHAR(10), DATETIME, 103)  DD/MM/YYYY 07/12/2021                
CONVERT(CHAR(10), DATETIME, 104)  DD.MM.YYYY 07.12.2021                
CONVERT(CHAR(10), DATETIME, 105) DD-MM-YYYY 07-12-2021                
CONVERT(CHAR(11), DATETIME, 106) DD MMM YYYY 07 Dec 2021               
CONVERT(CHAR(12), DATETIME, 107) MMM DD, YYYY Dec 07, 2021              
CONVERT(CHAR(8), DATETIME, 108) HH:MM:SS 12:47:32          
CONVERT(CHAR(26), DATETIME, 109) MMM D YYYY H:MM:SS:MS Dec  7 2021  1:13:25:323PM
CONVERT(CHAR(10), DATETIME, 110) MM-DD-YYYY 12-07-2021                
CONVERT(CHAR(10), DATETIME, 111) YYYY/MM/DD 2021/12/07                
CONVERT(CHAR(8), DATETIME, 112) YYYYMMDD 20211207                  
CONVERT(CHAR(24), DATETIME, 113) DD MMM YYYY HH:MM:SS:MS 07 Dec 2021 13:13:25:323  
CONVERT(CHAR(12), DATETIME, 114) HH:MM:SS:MS 13:13:25:323              
CONVERT(CHAR(19), DATETIME, 120) YYYY-MM-DD HH:MM:SS 2021-12-07 13:13:25       
CONVERT(CHAR(23), DATETIME, 121) YYYY-MM-DD HH:MM:SS,MS 2021-12-07 13:13:25.323   
CONVERT(CHAR(23), DATETIME, 126) YYYY-MM-DDTHH:MM:SS,MS 2021-12-07T13:13:25.323   
CONVERT(CHAR(23), DATETIME, 127) YYYY-MM-DDTHH:MM:SS,MS 2021-12-07T13:13:25.323   
CONVERT(CHAR(25), DATETIME, 131)    3/05/1443  1:13:25:323PM 

 

예제 Query

SELECT 'CONVERT(CHAR(19), DATETIME, 0)'		AS [Query], CONVERT(CHAR(19), GETDATE(), 0)		[RESULT]		, 1 sort		UNION ALL
SELECT 'CONVERT(CHAR(10), DATETIME, 1)'		AS [Query], CONVERT(CHAR(10), GETDATE(), 1)		[RESULT]		, 2 sort		UNION ALL
SELECT 'CONVERT(CHAR(8), DATETIME, 2)'		AS [Query], CONVERT(CHAR(8), GETDATE(), 2)		[RESULT]		, 3 sort		UNION ALL
SELECT 'CONVERT(CHAR(8), DATETIME, 3)'		AS [Query], CONVERT(CHAR(8), GETDATE(), 3)		[RESULT]		, 4 sort		UNION ALL
SELECT 'CONVERT(CHAR(8), DATETIME, 4)'		AS [Query], CONVERT(CHAR(8), GETDATE(), 4)		[RESULT]		, 5 sort		UNION ALL
SELECT 'CONVERT(CHAR(8), DATETIME, 5)'		AS [Query], CONVERT(CHAR(8), GETDATE(), 5)		[RESULT]		, 6 sort		UNION ALL
SELECT 'CONVERT(CHAR(8), DATETIME, 6)'		AS [Query], CONVERT(CHAR(8), GETDATE(), 6)		[RESULT]		, 7 sort		UNION ALL
SELECT 'CONVERT(CHAR(9), DATETIME, 7)'		AS [Query], CONVERT(CHAR(9), GETDATE(), 7)		[RESULT]		, 8 sort		UNION ALL
SELECT 'CONVERT(CHAR(8), DATETIME, 8)'		AS [Query], CONVERT(CHAR(8), GETDATE(), 8)		[RESULT]		, 9 sort		UNION ALL
SELECT 'CONVERT(CHAR(26), DATETIME, 9)'		AS [Query], CONVERT(CHAR(26), GETDATE(), 9)		[RESULT]		, 11 sort		UNION ALL
SELECT 'CONVERT(CHAR(8), DATETIME, 10)'		AS [Query], CONVERT(CHAR(8), GETDATE(), 10)		[RESULT]		, 12 sort		UNION ALL
SELECT 'CONVERT(CHAR(8), DATETIME, 11)'		AS [Query], CONVERT(CHAR(8), GETDATE(), 11)		[RESULT]		, 13 sort		UNION ALL
SELECT 'CONVERT(CHAR(6), DATETIME, 12)'		AS [Query], CONVERT(CHAR(6), GETDATE(), 12)		[RESULT]		, 14 sort		UNION ALL
SELECT 'CONVERT(CHAR(24), DATETIME, 13)'	AS [Query], CONVERT(CHAR(24), GETDATE(), 13)	[RESULT]		, 15 sort		UNION ALL
SELECT 'CONVERT(CHAR(12), DATETIME, 14)'	AS [Query], CONVERT(CHAR(12), GETDATE(), 14)	[RESULT]		, 16 sort		UNION ALL
SELECT 'CONVERT(CHAR(19), DATETIME, 20)'	AS [Query], CONVERT(CHAR(19), GETDATE(), 20)	[RESULT]		, 17 sort		UNION ALL
SELECT 'CONVERT(CHAR(23), DATETIME, 21)'	AS [Query], CONVERT(CHAR(23), GETDATE(), 21)	[RESULT]		, 18 sort		UNION ALL
SELECT 'CONVERT(CHAR(20), DATETIME, 22)'	AS [Query], CONVERT(CHAR(20), GETDATE(), 22)	[RESULT]		, 19 sort		UNION ALL
SELECT 'CONVERT(CHAR(10), DATETIME, 23)'	AS [Query], CONVERT(CHAR(10), GETDATE(), 23)	[RESULT]		, 20 sort		UNION ALL
SELECT 'CONVERT(CHAR(8), DATETIME, 24)'		AS [Query], CONVERT(CHAR(8), GETDATE(), 24)		[RESULT]		, 21 sort		UNION ALL
SELECT 'CONVERT(CHAR(23), DATETIME, 25)'	AS [Query], CONVERT(CHAR(23), GETDATE(), 25)	[RESULT]		, 22 sort		UNION ALL
SELECT 'CONVERT(CHAR(19), DATETIME, 100)'	AS [Query], CONVERT(CHAR(19), GETDATE(), 100)	[RESULT]		, 23 sort		UNION ALL
SELECT 'CONVERT(CHAR(10), DATETIME, 101)'	AS [Query], CONVERT(CHAR(10), GETDATE(), 101)	[RESULT]		, 24 sort		UNION ALL
SELECT 'CONVERT(CHAR(10), DATETIME, 102)'	AS [Query], CONVERT(CHAR(10), GETDATE(), 102)	[RESULT]		, 25 sort		UNION ALL
SELECT 'CONVERT(CHAR(10), DATETIME, 103)'	AS [Query], CONVERT(CHAR(10), GETDATE(), 103)	[RESULT]		, 26 sort		UNION ALL
SELECT 'CONVERT(CHAR(10), DATETIME, 104)'	AS [Query], CONVERT(CHAR(10), GETDATE(), 104)	[RESULT]		, 27 sort		UNION ALL
SELECT 'CONVERT(CHAR(10), DATETIME, 105)'	AS [Query], CONVERT(CHAR(10), GETDATE(), 105)	[RESULT]		, 28 sort		UNION ALL
SELECT 'CONVERT(CHAR(11), DATETIME, 106)'	AS [Query], CONVERT(CHAR(11), GETDATE(), 106)	[RESULT]		, 29 sort		UNION ALL
SELECT 'CONVERT(CHAR(12), DATETIME, 107)'	AS [Query], CONVERT(CHAR(12), GETDATE(), 107)	[RESULT]		, 30 sort		UNION ALL
SELECT 'CONVERT(CHAR(8), DATETIME, 108)'	AS [Query], CONVERT(CHAR(8), GETDATE(), 108)	[RESULT]		, 31 sort		UNION ALL
SELECT 'CONVERT(CHAR(26), DATETIME, 109)'	AS [Query], CONVERT(CHAR(26), GETDATE(), 109)	[RESULT]		, 32 sort		UNION ALL
SELECT 'CONVERT(CHAR(10), DATETIME, 110)'	AS [Query], CONVERT(CHAR(10), GETDATE(), 110)	[RESULT]		, 33 sort		UNION ALL
SELECT 'CONVERT(CHAR(10), DATETIME, 111)'	AS [Query], CONVERT(CHAR(10), GETDATE(), 111)	[RESULT]		, 34 sort		UNION ALL
SELECT 'CONVERT(CHAR(8), DATETIME, 112)'	AS [Query], CONVERT(CHAR(8), GETDATE(), 112)	[RESULT]		, 35 sort		UNION ALL
SELECT 'CONVERT(CHAR(24), DATETIME, 113)'	AS [Query], CONVERT(CHAR(24), GETDATE(), 113)	[RESULT]		, 36 sort		UNION ALL
SELECT 'CONVERT(CHAR(12), DATETIME, 114)'	AS [Query], CONVERT(CHAR(12), GETDATE(), 114)	[RESULT]		, 37 sort		UNION ALL
SELECT 'CONVERT(CHAR(19), DATETIME, 120)'	AS [Query], CONVERT(CHAR(19), GETDATE(), 120)	[RESULT]		, 38 sort		UNION ALL
SELECT 'CONVERT(CHAR(23), DATETIME, 121)'	AS [Query], CONVERT(CHAR(23), GETDATE(), 121)	[RESULT]		, 39 sort		UNION ALL
SELECT 'CONVERT(CHAR(23), DATETIME, 126)'	AS [Query], CONVERT(CHAR(23), GETDATE(), 126)	[RESULT]		, 40 sort		UNION ALL
SELECT 'CONVERT(CHAR(23), DATETIME, 127)'	AS [Query], CONVERT(CHAR(23), GETDATE(), 127)	[RESULT]		, 41 sort		UNION ALL
SELECT 'CONVERT(CHAR(25), DATETIME, 131)'	AS [Query], CONVERT(CHAR(25), GETDATE(), 131)	[RESULT]		, 42 sort
ORDER BY Sort
반응형