Get Standard Date Formats using SQL Server

Date Format	Standard	SQL Statement	Sample Output
Mon DD YYYY 1 HH:MIAM (or PM)	Default	SELECT CONVERT(VARCHAR(20), SYSDATETIME(), 100)	Jun 8 2011 1:30PM 1
MM/DD/YY	USA	SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 1) AS [MM/DD/YY]	06/08/11
MM/DD/YYYY	USA	SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 101) AS [MM/DD/YYYY]	06/08/11
YY.MM.DD	ANSI	SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 2) AS [YY.MM.DD]	11/06/08
YYYY.MM.DD	ANSI	SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 102) AS [YYYY.MM.DD]	06/08/11
DD/MM/YY	British/French	SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 3) AS [DD/MM/YY]	08/06/11
DD/MM/YYYY	British/French	SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 103) AS [DD/MM/YYYY]	08/06/11
DD.MM.YY	German	SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 4) AS [DD.MM.YY]	08/06/11
DD.MM.YYYY	German	SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 104) AS [DD.MM.YYYY]	08/06/11
DD-MM-YY	Italian	SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 5) AS [DD-MM-YY]	08/06/11
DD-MM-YYYY	Italian	SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 105) AS [DD-MM-YYYY]	08/06/11
DD Mon YY 1	-	SELECT CONVERT(VARCHAR(9), SYSDATETIME(), 6) AS [DD MON YY]	08 Jun 11 1
DD Mon YYYY 1	-	SELECT CONVERT(VARCHAR(11), SYSDATETIME(), 106) AS [DD MON YYYY]	08 Jun 2011 1
Mon DD, YY 1	-	SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 7) AS [Mon DD, YY]	Jun 08, 11 1
Mon DD, YYYY 1	-	SELECT CONVERT(VARCHAR(12), SYSDATETIME(), 107) AS [Mon DD, YYYY]	Jun 08, 2011 1
HH:MM:SS	-	SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 8) SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 108)	01:30:45 PM
Mon D YYYY H:MI:SS.NNNNNNNAM (or PM) 1	Default + nanoseconds	SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 9) SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 109)	Jun 8 2011 1:30:45.9428675PM 1
MM-DD-YY	USA	SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 10) AS [MM-DD-YY]	06/08/11
MM-DD-YYYY	USA	SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 110) AS [MM-DD-YYYY]	06/08/11
YY/MM/DD	-	SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 11) AS [YY/MM/DD]	11/06/08
YYYY/MM/DD	-	SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 111) AS [YYYY/MM/DD]	06/08/11
YYMMDD	ISO	SELECT CONVERT(VARCHAR(6), SYSDATETIME(), 12) AS [YYMMDD]	110608
YYYYMMDD	ISO	SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 112) AS [YYYYMMDD]	20110608
DD Mon YYYY HH:MM:SS.NNNNNNN(24h) 1	Europe default + nanoseconds	SELECT CONVERT(VARCHAR(30), SYSDATETIME(), 13) SELECT CONVERT(VARCHAR(30), SYSDATETIME(), 113)	08 Jun 2011 13:30:45.94286751
HH:MI:SS.NNNNNNN(24H)	-	SELECT CONVERT(VARCHAR(16), SYSDATETIME(), 14) AS [HH:MI:SS:MMM(24H)] SELECT CONVERT(VARCHAR(16), SYSDATETIME(), 114) AS [HH:MI:SS:MMM(24H)]	13:30:45.94
YYYY-MM-DD HH:MI:SS(24h)	ODBC Canonical	SELECT CONVERT(VARCHAR(19), SYSDATETIME(), 120)	06/08/11 01:30 PM
YYYY-MM-DD HH:MI:SS.NNNNNNN(24h)	ODBC Canonical (with nanoseconds)	SELECT CONVERT(VARCHAR(23), SYSDATETIME(), 121)	06/08/11 01:30 PM
MM/DD/YY HH:MI:SS AM	-	SELECT CONVERT(VARCHAR(20), SYSDATETIME(), 22)	06/08/11 01:30 PM
YYYY-MM-DD	-	SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 23)	2011-06-091
HH:MI:SS (24h)	-	SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 24)	01:30:45 PM
YYYY-MM-DD HH:MI:SS.NNNNNNN	-	SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 25)	06/08/11 01:30 PM
YYYY-MM-DDTHH:MM:SS:NNNNNNN	ISO8601	SELECT CONVERT(VARCHAR(27), SYSDATETIME(), 126)	06/08/11 01:30 PM
DD Mon YYYY HH:MI:SS.NNNNNNNAM 1	Kuwaiti	SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 130)	08 Jun 2011 1:30:45.9428675PM1
DD/MM/YYYY HH:MI:SS.NNNNNNNAM	Kuwaiti	SELECT CONVERT(VARCHAR(25), SYSDATETIME(), 131)	08/06/11 01:30 PM