SQL Server Date Formats

Biswajeet   May 23, 2014   No Comments on SQL Server Date Formats
Date Format Standard SQL Query Output
MM/DD/YY USA SELECT CONVERT(VARCHAR(8), GETDATE(), 1) 05/23/14
MM/DD/YYYY USA SELECT CONVERT(VARCHAR(10), GETDATE(), 101) 05/23/2014
YY.MM.DD ANSI SELECT CONVERT(VARCHAR(8), GETDATE(), 2) 14.05.23
YYYY.MM.DD ANSI SELECT CONVERT(VARCHAR(10), GETDATE(), 102) 2014.05.23
DD/MM/YY British/French SELECT CONVERT(VARCHAR(8), GETDATE(), 3) 23/05/14
DD/MM/YYYY British/French SELECT CONVERT(VARCHAR(10), GETDATE(), 103) 23/05/2014
DD.MM.YY German SELECT CONVERT(VARCHAR(8), GETDATE(), 4) 23.05.14
DD.MM.YYYY German SELECT CONVERT(VARCHAR(10), GETDATE(), 104) 23.05.2014
DD-MM-YY Italian SELECT CONVERT(VARCHAR(8), GETDATE(), 5) 23-05-14
DD-MM-YYYY Italian SELECT CONVERT(VARCHAR(10), GETDATE(), 105) 23-05-2014
HH:MM:SS   SELECT CONVERT(VARCHAR(8), GETDATE(), 108) 07:28:42
MM-DD-YY USA SELECT CONVERT(VARCHAR(8), GETDATE(), 10) 05-23-14
MM-DD-YYYY USA SELECT CONVERT(VARCHAR(10), GETDATE(), 110) 05-23-2014
YY/MM/DD Japan SELECT CONVERT(VARCHAR(8), GETDATE(), 11) 14/05/23
YYYY/MM/DD Japan SELECT CONVERT(VARCHAR(10), GETDATE(), 111) 2014/05/23
YYMMDD ISO SELECT CONVERT(VARCHAR(6), GETDATE(), 12) 140523
YYYYMMDD ISO SELECT CONVERT(VARCHAR(8), GETDATE(), 112) 20140523
HH:MI:SS:MMM(24H)   SELECT CONVERT(VARCHAR(12), GETDATE(), 114) 07:30:15:913
YYYY-MM-DD HH:MI:SS(24h) ODBC Canonical SELECT CONVERT(VARCHAR(19), GETDATE(), 120) 2014-05-23 07:30:28
YYYY-MM-DD HH:MI:SS.MMM(24h) ODBC Canonical
(with milliseconds)		 SELECT CONVERT(VARCHAR(23), GETDATE(), 121) 2014-05-23 07:30:49.337
YYYY-MM-DDTHH:MM:SS:MMM ISO8601 SELECT CONVERT(VARCHAR(23), GETDATE(), 126) 2014-05-23T07:31:03.303
DD/MM/YYYY HH:MI:SS:MMMAM Hijiri SELECT CONVERT(VARCHAR(25), GETDATE(), 131) 24/07/1435 7:31:16:803AM