SQL Server - Formatting Date/Time

Formatting date time in SQL Server is done using the convert function. It requires knowing the length of the output string and the format code number.

SQL Server Date/Time Formatting - US

Format	Output	Statement
MM/DD/YY	03/04/13	CONVERT(VARCHAR(8), GETDATE(), 1)
MM/DD/YYYY	03/04/2013	CONVERT(VARCHAR(10), GETDATE(), 101)
MM-DD-YY	03-04-13	CONVERT(VARCHAR(8), GETDATE(), 10)
MM-DD-YYYY	03-04-2013	CONVERT(VARCHAR(10), GETDATE(), 110)

SELECT TOP 1
-- MM/DD/YY
  CONVERT(VARCHAR(8),   GETDATE(), 1)    AS "1"
-- MM/DD/YYYY
 ,CONVERT(VARCHAR(10),  GETDATE(), 101)  AS "101"
-- MM-DD-YY
 ,CONVERT(VARCHAR(8),   GETDATE(), 10)   AS "10"
-- MM-DD-YYYY
 ,CONVERT(VARCHAR(10),  GETDATE(), 110)  AS "110" 
FROM AdventureWorks2008R2.Sales.Store

SQL Server Date/Time Formatting - European

Format	Output	Statement
DD/MM/YY	23/05/13	CONVERT(VARCHAR(8), GETDATE(), 3)
DD/MM/YYYY	23/05/2013	CONVERT(VARCHAR(10), GETDATE(), 103)
DD.MM.YY	23.05.13	CONVERT(VARCHAR(8), GETDATE(), 4)
DD.MM.YYYY	23.05.2013	CONVERT(VARCHAR(10), GETDATE(), 104)
DD-MM-YY	23-05-13	CONVERT(VARCHAR(8), GETDATE(), 5)
DD-MM-YYYY	23-05-2013	CONVERT(VARCHAR(8), GETDATE(), 105)
	04 Mar 2013 17:27:09:113	CONVERT(VARCHAR(24), GETDATE(), 113)

SELECT TOP 1
   CONVERT(VARCHAR(24), GETDATE(), 113) AS "113"
-- UK / France
-- DD/MM/YY
 ,CONVERT(VARCHAR(8),   GETDATE(), 3)    AS "3"
-- DD/MM/YYYY 
 ,CONVERT(VARCHAR(10),  GETDATE(), 103)  AS "103"
-- German
-- DD.MM.YY
 ,CONVERT(VARCHAR(8),   GETDATE(), 4)    AS "4"
-- DD.MM.YYYY
 ,CONVERT(VARCHAR(10),  GETDATE(), 104)  AS "104" 
-- Italian
-- DD-MM-YY
 ,CONVERT(VARCHAR(8),   GETDATE(), 5)    AS "5"
-- DD-MM-YYYY
 ,CONVERT(VARCHAR(10),  GETDATE(), 105)  AS "105" 
FROM AdventureWorks2008R2.Sales.Store

SQL Server - Format Time

Format	Output	Statement
HH:MM:SS	17:30:45	CONVERT(VARCHAR(10), GETDATE(), 108)

SELECT TOP 1
-- HH:MM:SS
 CONVERT(VARCHAR(10),  GETDATE(), 108)  AS "108"  
FROM AdventureWorks2008R2.Sales.Store

SQL Server - Date/Time

Format	Output	Statement
Mon DD YYYY HH:MM[AM|PM]	Mar 4 2013 6:00PM	CONVERT(VARCHAR(20), GETDATE(), 100)
Mon DD YYYY HH:MM:SS:MMM[AM|PM]	Mar 4 2013 6:00:32:330PM	CONVERT(VARCHAR(26), GETDATE(), 109)
YYYY-MM-DD HH:MM:DD	2013-03-04 18:00:32	CONVERT(VARCHAR(19), GETDATE(), 120)
YYYY-MM-DD HH:MM:DD:MMM	2013-03-04 18:00:32.330	CONVERT(VARCHAR(23), GETDATE(), 121)
YYYY-MM-DDTHH:MM:DD:MMM	2013-03-04T18:00:32.330	CONVERT(VARCHAR(23), GETDATE(), 126)

SELECT TOP 1
-- Mon DD YYYY HH:MM[AM|PM]
  CONVERT(VARCHAR(20),  GETDATE(), 100)  AS "100"
-- Mon DD YYYY HH:MM:SS:MMM[AM|PM]
 ,CONVERT(VARCHAR(26),  GETDATE(), 109)  AS "109"
-- YYYY-MM-DD HH:MM:DD
 ,CONVERT(VARCHAR(19),  GETDATE(), 120)  AS "120"     
-- YYYY-MM-DD HH:MM:DD:MMM
 ,CONVERT(VARCHAR(23),  GETDATE(), 121)  AS "121"  
-- YYYY-MM-DDTHH:MM:DD:MMM
 ,CONVERT(VARCHAR(23),  GETDATE(), 126)  AS "126"   
FROM AdventureWorks2008R2.Sales.Store

SQL Server Date/Time Formatting - Other

Format	Output	Statement
DD Mon YY	03 Mar 13	CONVERT(VARCHAR(9), GETDATE(), 6)
DD Mon YYYY	03 Mar 2013	CONVERT(VARCHAR(11), GETDATE(), 106)
Mon DD, YY	Mar 04, 13	CONVERT(VARCHAR(10), GETDATE(), 7)
Mon DD, YYYY	Mar 04, 2013	CONVERT(VARCHAR(12), GETDATE(), 107)
YY.MM.DD	13.03.04	CONVERT(VARCHAR(8), GETDATE(), 2)
YYYY.MM.DD	2013.03.04	CONVERT(VARCHAR(10), GETDATE(), 102)
YY/MM/DD	13/03/04	CONVERT(VARCHAR(8), GETDATE(), 11)
YYYY/MM/DD	2013/03/04	CONVERT(VARCHAR(10), GETDATE(), 111)
YYMMDD	130304	CONVERT(VARCHAR(6), GETDATE(), 12)
YYYYMMDD	20130304	CONVERT(VARCHAR(8), GETDATE(), 112)

SELECT TOP 1
-- DD Mon YY
  CONVERT(VARCHAR(9),   GETDATE(),   6)  AS "6" 
-- DD Mon YYYY
 ,CONVERT(VARCHAR(11),  GETDATE(), 106)  AS "106"
-- Mon DD, YY
 ,CONVERT(VARCHAR(10),  GETDATE(),   7)  AS "7"
-- Mon DD, YYYY
 ,CONVERT(VARCHAR(12),  GETDATE(), 107)  AS "107" 
-- YY.MM.DD
 ,CONVERT(VARCHAR(8),   GETDATE(),   2)  AS "2"
-- YYYY.MM.DD
 ,CONVERT(VARCHAR(10),  GETDATE(), 102)  AS "102"
-- YY/MM/DD
 ,CONVERT(VARCHAR(8),   GETDATE(),  11)  AS "11" 
-- YYYY/MM/DD
 ,CONVERT(VARCHAR(10),  GETDATE(), 111)  AS "111"
-- YYMMDD
 ,CONVERT(VARCHAR(6),   GETDATE(),  12)  AS "12"
-- YYYYMMDD
 ,CONVERT(VARCHAR(8),   GETDATE(), 112)  AS "112" 
FROM AdventureWorks2008R2.Sales.Store