January 13, 2011

Date format in SQL


 Mon DD YYYY 1  
 HH:MIAM (or PM)  
 Default  
 SELECT CONVERT(VARCHAR(20), GETDATE(), 100)  
 Jan 1 2005 1:29PM 1  
 MM/DD/YY  
 USA  
 SELECT CONVERT(VARCHAR(8), GETDATE(), 1) AS [MM/DD/YY]  
 11/23/98  
 MM/DD/YYYY  
 USA  
 SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]  
 11/23/1998  
 YY.MM.DD  
 ANSI  
 SELECT CONVERT(VARCHAR(8), GETDATE(), 2) AS [YY.MM.DD]  
 72.01.01  
 YYYY.MM.DD  
 ANSI  
 SELECT CONVERT(VARCHAR(10), GETDATE(), 102) AS [YYYY.MM.DD]  
 1972.01.01  
 DD/MM/YY  
 British/French  
 SELECT CONVERT(VARCHAR(8), GETDATE(), 3) AS [DD/MM/YY]  
 19/02/72  
 DD/MM/YYYY  
 British/French  
 SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY]  
 19/02/1972  
 DD.MM.YY  
 German  
 SELECT CONVERT(VARCHAR(8), GETDATE(), 4) AS [DD.MM.YY]  
 25.12.05  
 DD.MM.YYYY  
 German  
 SELECT CONVERT(VARCHAR(10), GETDATE(), 104) AS [DD.MM.YYYY]  
 25.12.2005  
 DD-MM-YY  
 Italian  
 SELECT CONVERT(VARCHAR(8), GETDATE(), 5) AS [DD-MM-YY]  
 24-01-98  
 DD-MM-YYYY  
 Italian  
 SELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY]  
 24-01-1998  
 DD Mon YY 1  
 -  
 SELECT CONVERT(VARCHAR(9), GETDATE(), 6) AS [DD MON YY]  
 04 Jul 06 1  
 DD Mon YYYY 1  
 -  
 SELECT CONVERT(VARCHAR(11), GETDATE(), 106) AS [DD MON YYYY]  
 04 Jul 2006 1  
 Mon DD, YY 1  
 -  
 SELECT CONVERT(VARCHAR(10), GETDATE(), 7) AS [Mon DD, YY]  
 Jan 24, 98 1  
 Mon DD, YYYY 1  
 -  
 SELECT CONVERT(VARCHAR(12), GETDATE(), 107) AS [Mon DD, YYYY]  
 Jan 24, 1998 1  
 HH:MM:SS  
 -  
 SELECT CONVERT(VARCHAR(8), GETDATE(), 108)  
 03:24:53  
 Mon DD YYYY HH:MI:SS:MMMAM (or PM) 1  
 Default +milliseconds  
 SELECT CONVERT(VARCHAR(26), GETDATE(), 109)  
 Apr 28 2006 12:32:29:253PM 1  
 MM-DD-YY  
 USA  
 SELECT CONVERT(VARCHAR(8), GETDATE(), 10) AS [MM-DD-YY]  
 01-01-06  
 MM-DD-YYYY  
 USA  
 SELECT CONVERT(VARCHAR(10), GETDATE(), 110) AS [MM-DD-YYYY]  
 01-01-2006  
 YY/MM/DD  
 -  
 SELECT CONVERT(VARCHAR(8), GETDATE(), 11) AS [YY/MM/DD]  
 98/11/23  
 YYYY/MM/DD  
 -  
 SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD]  
 1998/11/23  
 YYMMDD  
 ISO  
 SELECT CONVERT(VARCHAR(6), GETDATE(), 12) AS [YYMMDD]  
 980124  
 YYYYMMDD  
 ISO  
 SELECT CONVERT(VARCHAR(8), GETDATE(), 112) AS [YYYYMMDD]  
 19980124  
 DD Mon YYYY HH:MM:SS:MMM(24h) 1  
 Europe default + milliseconds  
 SELECT CONVERT(VARCHAR(24), GETDATE(), 113)  
 28 Apr 2006 00:34:55:190 1  
 HH:MI:SS:MMM(24H)  
 -  
 SELECT CONVERT(VARCHAR(12), GETDATE(), 114) AS [HH:MI:SS:MMM(24H)]  
 11:34:23:013  
 YYYY-MM-DD HH:MI:SS(24h)  
 ODBC Canonical  
 SELECT CONVERT(VARCHAR(19), GETDATE(), 120)  
 1972-01-01 13:42:24  
 YYYY-MM-DD HH:MI:SS.MMM(24h)  
 ODBC Canonical  
 (with milliseconds)  
 SELECT CONVERT(VARCHAR(23), GETDATE(), 121)  
 1972-02-19 06:35:24.489  
 YYYY-MM-DDTHH:MM:SS:MMM  
 ISO8601  
 SELECT CONVERT(VARCHAR(23), GETDATE(), 126)  
 1998-11-23T11:25:43:250  
 DD Mon YYYY HH:MI:SS:MMMAM 1  
 Kuwaiti  
 SELECT CONVERT(VARCHAR(26), GETDATE(), 130)  
 28 Apr 2006 12:39:32:429AM 1  
 DD/MM/YYYY HH:MI:SS:MMMAM  
 Kuwaiti  
 SELECT CONVERT(VARCHAR(25), GETDATE(), 131)  
 28/04/2006 12:39:32:429AM