SQL Server Date Formats

jj prinsloo sql date time formats featured

One of the most frequently searched terms regarding SQL server is SQL Server Date Formats and codes as well as how to format a date for a specific requirement or standard. Although it is one of the first things you learn when working with SQL server, it it is not always as easy to remember all the format codes which is to be used as part of the CONVERT function to accomplish the desired result.

Here is a complete reference to the date formats that come standard with SQL Server. Following the standard format list is a list of extended formats that are often required. Note that these extended formats are not returned as a DATETIME data type but rather a VARCHAR data type so you will have to perform all your date calculations and modifications before converting it to an extended format. Happy dating!

Standard SQL Date Formats

Date FormatStandardSQL StatementSample Output
Mon DD YYYY HH:MIAM (or PM)DefaultSELECT CONVERT(VARCHAR(20), GETDATE(), 100)Jan 1 2005 1:29PM
MM/DD/YYUSASELECT CONVERT(VARCHAR(8), GETDATE(), 1) AS [MM/DD/YY]11/23/98
MM/DD/YYYYUSASELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]11/23/1998
YY.MM.DDANSISELECT CONVERT(VARCHAR(8), GETDATE(), 2) AS [YY.MM.DD]72.01.01
YYYY.MM.DDANSISELECT CONVERT(VARCHAR(10), GETDATE(), 102) AS [YYYY.MM.DD]1972.01.01
DD/MM/YYBritish/FrenchSELECT CONVERT(VARCHAR(8), GETDATE(), 3) AS [DD/MM/YY]19/02/72
DD/MM/YYYYBritish/FrenchSELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY]19/02/1972
DD.MM.YYGermanSELECT CONVERT(VARCHAR(8), GETDATE(), 4) AS [DD.MM.YY]25.12.05
DD.MM.YYYYGermanSELECT CONVERT(VARCHAR(10), GETDATE(), 104) AS [DD.MM.YYYY]25.12.2005
DD-MM-YYItalianSELECT CONVERT(VARCHAR(8), GETDATE(), 5) AS [DD-MM-YY]24-01-98
DD-MM-YYYYItalianSELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY]24-01-1998
DD Mon YYSELECT CONVERT(VARCHAR(9), GETDATE(), 6) AS [DD MON YY]04 Jul 06
DD Mon YYYYSELECT CONVERT(VARCHAR(11), GETDATE(), 106) AS [DD MON YYYY]04 Jul 2006
Mon DD, YYSELECT CONVERT(VARCHAR(10), GETDATE(), 7) AS [Mon DD, YY]Jan 24, 98
Mon DD, YYYYSELECT CONVERT(VARCHAR(12), GETDATE(), 107) AS [Mon DD, YYYY]Jan 24, 1998
HH:MM:SSSELECT CONVERT(VARCHAR(8), GETDATE(), 108)03:24:53
Mon DD YYYY HH:MI:SS:MMMAM (or PM)Default + millisecondsSELECT CONVERT(VARCHAR(26), GETDATE(), 109)Apr 28 2006 12:32:29:253PM
MM-DD-YYUSASELECT CONVERT(VARCHAR(8), GETDATE(), 10) AS [MM-DD-YY]01-01-06
MM-DD-YYYYUSASELECT CONVERT(VARCHAR(10), GETDATE(), 110) AS [MM-DD-YYYY]01-01-2006
YY/MM/DDSELECT CONVERT(VARCHAR(8), GETDATE(), 11) AS [YY/MM/DD]98/11/23
YYYY/MM/DDSELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD]1998/11/23
YYMMDDISOSELECT CONVERT(VARCHAR(6), GETDATE(), 12) AS [YYMMDD]980124
YYYYMMDDISOSELECT CONVERT(VARCHAR(8), GETDATE(), 112) AS [YYYYMMDD]19980124
DD Mon YYYY HH:MM:SS:MMM(24h)Europe default + millisecondsSELECT CONVERT(VARCHAR(24), GETDATE(), 113)28 Apr 2006 00:34:55:190
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 CanonicalSELECT 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:MMMISO8601SELECT CONVERT(VARCHAR(23), GETDATE(), 126)1998-11-23T11:25:43:250
DD Mon YYYY HH:MI:SS:MMMAMKuwaitiSELECT CONVERT(VARCHAR(26), GETDATE(), 130)28 Apr 2006 12:39:32:429AM
DD/MM/YYYY HH:MI:SS:MMMAMKuwaitiSELECT CONVERT(VARCHAR(25), GETDATE(), 131)28/04/2006 12:39:32:429AM

 

Extended Date Formats

Date FormatSQL StatementSample Output
YY-MM-DDSELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 8) AS [YY-MM-DD] SELECT REPLACE(CONVERT(VARCHAR(8), GETDATE(), 11), ‘/’, ‘-‘) AS [YY-MM-DD]99-01-24
YYYY-MM-DDSELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS [YYYY-MM-DD] SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111), ‘/’, ‘-‘) AS [YYYY-MM-DD]1999-01-24
MM/YYSELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 3), 5) AS [MM/YY] SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 3), 4, 5) AS [MM/YY]08/99
MM/YYYYSELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 103), 7) AS [MM/YYYY]12/2005
YY/MMSELECT CONVERT(VARCHAR(5), GETDATE(), 11) AS [YY/MM]99/08
YYYY/MMSELECT CONVERT(VARCHAR(7), GETDATE(), 111) AS [YYYY/MM]2005/12
Month DD, YYYYSELECT DATENAME(MM, GETDATE()) + RIGHT(CONVERT(VARCHAR(12), GETDATE(), 107), 9) AS [Month DD, YYYY]July 04, 2006
Mon YYYYSELECT SUBSTRING(CONVERT(VARCHAR(11), GETDATE(), 113), 4, 8) AS [Mon YYYY]Apr 2006
Month YYYYSELECT DATENAME(MM, GETDATE()) + ‘ ‘ + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [Month YYYY]February 2006
DD MonthSELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ‘ ‘ + DATENAME(MM, GETDATE()) AS [DD Month]11 September
Month DDSELECT DATENAME(MM, GETDATE()) + ‘ ‘ + CAST(DAY(GETDATE()) AS VARCHAR(2)) AS [Month DD]September 11
DD Month YYSELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ‘ ‘ + DATENAME(MM, GETDATE()) + ‘ ‘ + RIGHT(CAST(YEAR(GETDATE()) AS VARCHAR(4)), 2) AS [DD Month YY]19 February 72
DD Month YYYYSELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ‘ ‘ + DATENAME(MM, GETDATE()) + ‘ ‘ + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [DD Month YYYY]11 September 2002
MM-YYSELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 5), 5) AS [MM-YY] SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 5), 4, 5) AS [MM-YY]12/92
MM-YYYYSELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 105), 7) AS [MM-YYYY]05-2006
YY-MMSELECT RIGHT(CONVERT(VARCHAR(7), GETDATE(), 120), 5) AS [YY-MM] SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 5) AS [YY-MM]92/12
YYYY-MMSELECT CONVERT(VARCHAR(7), GETDATE(), 120) AS [YYYY-MM]2006-05
MMDDYYSELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 1), ‘/’, ”) AS [MMDDYY]122506
MMDDYYYYSELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), ‘/’, ”) AS [MMDDYYYY]12252006
DDMMYYSELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 3), ‘/’, ”) AS [DDMMYY]240702
DDMMYYYYSELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103), ‘/’, ”) AS [DDMMYYYY]24072002
Mon-YYSELECT REPLACE(RIGHT(CONVERT(VARCHAR(9), GETDATE(), 6), 6), ‘ ‘, ‘-‘) AS [Mon-YY]Sep-02
Mon-YYYYSELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), GETDATE(), 106), 8), ‘ ‘, ‘-‘) AS [Mon-YYYY]Sep-2002
DD-Mon-YY SELECT REPLACE(CONVERT(VARCHAR(9), GETDATE(), 6), ‘ ‘, ‘-‘) AS [DD-Mon-YY]25-Dec-05
DD-Mon-YYYYSELECT REPLACE(CONVERT(VARCHAR(11), GETDATE(), 106), ‘ ‘, ‘-‘) AS [DD-Mon-YYYY]25-Dec-2005
0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *