How to format datetime value or column into a specific date format. Is the most frequently asked question by the newbie. Below table is summary of the different date formats that come from SQL Server with the CONVERT function.
Please note that the output of these date formats are VARCHAR data type, not DATETIME data type. With this in mind, any date comparisons performed after the DATETIME value has been formatted are using the VARCHAR value of the date and time and not its original DATETIME value.
| Date Format | SQL Statement | Output |
|---|---|---|
| Mon DD YYYY HH:MIAM (or PM) | SELECT CONVERT(VARCHAR(20), GETDATE(), 100) | Jan 1 2014 1:29PM |
| MM/DD/YY | SELECT CONVERT(VARCHAR(8), GETDATE(), 1) | 11/23/98 |
| MM/DD/YYYY | SELECT CONVERT(VARCHAR(10), GETDATE(), 101) | 11/23/1998 |
| YY.MM.DD | SELECT CONVERT(VARCHAR(8), GETDATE(), 2) | 98.11.23 |
| YYYY.MM.DD | SELECT CONVERT(VARCHAR(10), GETDATE(), 102) | 1998.11.23 |
| DD/MM/YY | SELECT CONVERT(VARCHAR(8), GETDATE(), 3) | 23/11/98 |
| DD/MM/YYYY | SELECT CONVERT(VARCHAR(10), GETDATE(), 103) | 23/11/1998 |
| DD.MM.YY | SELECT CONVERT(VARCHAR(8), GETDATE(), 4) | 23.11.98 |
| DD.MM.YYYY | SELECT CONVERT(VARCHAR(10), GETDATE(), 104) | 23.11.1998 |
| DD-MM-YY | SELECT CONVERT(VARCHAR(8), GETDATE(), 5) | 23-11-98 |
| DD-MM-YYYY | SELECT CONVERT(VARCHAR(10), GETDATE(), 105) | 23-11-1998 |
| DD Mon YY | SELECT CONVERT(VARCHAR(9), GETDATE(), 6) | 04 Jul 98 |
| DD Mon YYYY | SELECT CONVERT(VARCHAR(11), GETDATE(), 106) | 04 Jul 1998 |
| Mon DD, YY | SELECT CONVERT(VARCHAR(10), GETDATE(), 7) | Nov 24, 98 |
| Mon DD, YYYY | SELECT CONVERT(VARCHAR(12), GETDATE(), 107) | Nov 24, 1998 |
| HH:MM:SS | SELECT CONVERT(VARCHAR(8), GETDATE(), 108) | 03:24:53 |
| Mon DD YYYY HH:MI:SS:MMMAM (or PM) | SELECT CONVERT(VARCHAR(26), GETDATE(), 109) | Apr 28 2006 12:32:29:253PM |
| MM-DD-YY | SELECT CONVERT(VARCHAR(8), GETDATE(), 10) | 11-23-98 |
| MM-DD-YYYY | SELECT CONVERT(VARCHAR(10), GETDATE(), 110) | 11-23-1998 |
| YY/MM/DD | SELECT CONVERT(VARCHAR(8), GETDATE(), 11) | 98/11/23 |
| YYYY/MM/DD | SELECT CONVERT(VARCHAR(10), GETDATE(), 111) | 1998/11/23 |
| YYMMDD | SELECT CONVERT(VARCHAR(6), GETDATE(), 12) | 981124 |
| YYYYMMDD | SELECT CONVERT(VARCHAR(8), GETDATE(), 112) | 19981124 |
| DD Mon YYYY HH:MM:SS:MMM(24h) | SELECT CONVERT(VARCHAR(24), GETDATE(), 113) | 28 Apr 1998 00:34:55:190 |
| HH:MI:SS:MMM(24H) | SELECT CONVERT(VARCHAR(12), GETDATE(), 114) | 11:34:23:013 |
| YYYY-MM-DD HH:MI:SS(24h) | SELECT CONVERT(VARCHAR(19), GETDATE(), 120) | 1998-11-23 13:42:24 |
| YYYY-MM-DD HH:MI:SS.MMM(24h) | SELECT CONVERT(VARCHAR(23), GETDATE(), 121) | 1998-11-23 06:35:24.489 |
| YYYY-MM-DDTHH:MM:SS:MMM | SELECT CONVERT(VARCHAR(23), GETDATE(), 126) | 1998-11-23T11:25:43:250 |
| DD Mon YYYY HH:MI:SS:MMMAM | SELECT CONVERT(VARCHAR(26), GETDATE(), 130) | 23 Nov 1998 12:39:32:429AM |
| DD/MM/YYYY HH:MI:SS:MMMAM | SELECT CONVERT(VARCHAR(25), GETDATE(), 131) | 23/11.1998 12:39:32:429AM |
Extended Date Formats
Below are some more date formats that does not come standard in SQL Server as part of the CONVERT function.| Date Format | SQL Statement | Output |
|---|---|---|
| YY-MM-DD |
SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 8) SELECT REPLACE(CONVERT(VARCHAR(8), GETDATE(), 11), ‘/’, ‘-‘) |
99-08-24 |
| YYYY-MM-DD |
SELECT CONVERT(VARCHAR(10), GETDATE(), 120) SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111), ‘/’, ‘-‘) |
1999-08-24 |
| MM/YY |
SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 3), 5) SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 3), 4, 5) |
08/99 |
| MM/YYYY | SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 103), 7) | 08/1999 |
| YY/MM | SELECT CONVERT(VARCHAR(5), GETDATE(), 11) | 99/08 |
| YYYY/MM | SELECT CONVERT(VARCHAR(7), GETDATE(), 111) | 1999/08 |
| Month DD, YYYY | SELECT DATENAME(MM, GETDATE()) + RIGHT(CONVERT(VARCHAR(12), GETDATE(), 107), 9) | August 24, 1999 |
| Mon YYYY | SELECT SUBSTRING(CONVERT(VARCHAR(11), GETDATE(), 113), 4, 8) | Aug 1999 |
| Month YYYY | SELECT DATENAME(MM, GETDATE()) + ‘ ‘ + CAST(YEAR(GETDATE()) | August 1999 |
| DD Month | SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ‘ ‘ + DATENAME(MM, GETDATE()) | 24 August |
| Month DD | SELECT DATENAME(MM, GETDATE()) + ‘ ‘ + CAST(DAY(GETDATE()) AS VARCHAR(2)) | August 24 |
| DD Month YY | SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ‘ ‘ + DATENAME(MM, GETDATE()) + ‘ ‘ + RIGHT(CAST(YEAR(GETDATE()) AS VARCHAR(4)), 2) | 24 August 99 |
| DD Month YYYY | SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ‘ ‘ + DATENAME(MM, GETDATE()) + ‘ ‘ + CAST(YEAR(GETDATE()) AS VARCHAR(4)) | 24 August 1999 |
| MM-YY |
SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 5), 5) SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 5), 4, 5) |
08/99 |
| MM-YYYY | SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 105), 7) | 08-1999 |
| YY-MM |
SELECT RIGHT(CONVERT(VARCHAR(7), GETDATE(), 120), 5) SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 5) |
99-08 |
| YYYY-MM | SELECT CONVERT(VARCHAR(7), GETDATE(), 120) | 1999-08 |
| MMDDYY | SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 1), ‘/’, ”) | 082499 |
| MMDDYYYY | SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), ‘/’, ”) | 08241999 |
| DDMMYY | SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 3), ‘/’, ”) | 240899 |
| DDMMYYYY | SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103), ‘/’, ”) | 24081999 |
| Mon-YY | SELECT REPLACE(RIGHT(CONVERT(VARCHAR(9), GETDATE(), 6), 6), ‘ ‘, ‘-‘) | Aug-99 |
| Mon-YYYY | SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), GETDATE(), 106), 8), ‘ ‘, ‘-‘) | Aug-1999 |
| DD-Mon-YY | SELECT REPLACE(CONVERT(VARCHAR(9), GETDATE(), 6), ‘ ‘, ‘-‘) | 24-Aug-99 |
| DD-Mon-YYYY | SELECT REPLACE(CONVERT(VARCHAR(11), GETDATE(), 106), ‘ ‘, ‘-‘) | 24-Aug-1999 |