SQL Server Date Formats

There is has been lots of search I was doing with MSSQL datetime format. Here I have listed summary of the various date formats that come standard in SQL Server as part of function called CONVERT. There has been extended list of date which being asked by experienced sql developer.

Please note that the output of these function is VARCHAR (text) data types and not a DATETIME.

Standard Date Formats
Date FormatStandardSQL StatementSample Output
Mon DD YYYY 1
HH:MIAM (or PM)
DefaultSELECT CONVERT(VARCHAR(20), GETDATE(), 100)Jan 1 2005 1:29PM 1
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 YY 1SELECT CONVERT(VARCHAR(9), GETDATE(), 6) AS [DD MON YY]04 Jul 06 1
DD Mon YYYY 1SELECT CONVERT(VARCHAR(11), GETDATE(), 106) AS [DD MON YYYY]04 Jul 2006 1
Mon DD, YY 1SELECT CONVERT(VARCHAR(10), GETDATE(), 7) AS [Mon DD, YY]Jan 24, 98 1
Mon DD, YYYY 1SELECT CONVERT(VARCHAR(12), GETDATE(), 107) AS [Mon DD, YYYY]Jan 24, 1998 1
HH:MM:SSSELECT CONVERT(VARCHAR(8), GETDATE(), 108)03:24:53
Mon DD YYYY HH:MI:SS:MMMAM (or PM) 1Default +
milliseconds
SELECT CONVERT(VARCHAR(26), GETDATE(), 109)Apr 28 2006 12:32:29:253PM 1
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) 1Europe default + millisecondsSELECT 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 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:MMMAM 1KuwaitiSELECT CONVERT(VARCHAR(26), GETDATE(), 130)28 Apr 2006 12:39:32:429AM 1
DD/MM/YYYY HH:MI:SS:MMMAMKuwaitiSELECT CONVERT(VARCHAR(25), GETDATE(), 131)28/04/2006 12:39:32:429AM

Here are some more date formats that does not come standard in SQL Server as part of the CONVERT function.

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, YYYY 1SELECT DATENAME(MM, GETDATE()) + RIGHT(CONVERT(VARCHAR(12), GETDATE(), 107), 9) AS [Month DD, YYYY]July 04, 2006 1
Mon YYYY 1SELECT SUBSTRING(CONVERT(VARCHAR(11), GETDATE(), 113), 4, 8) AS [Mon YYYY]Apr 2006 1
Month YYYY 1SELECT DATENAME(MM, GETDATE()) + ‘ ‘ + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [Month YYYY]February 2006 1
DD Month 1SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ‘ ‘ + DATENAME(MM, GETDATE()) AS [DD Month]11 September 1
Month DD 1SELECT DATENAME(MM, GETDATE()) + ‘ ‘ + CAST(DAY(GETDATE()) AS VARCHAR(2)) AS [Month DD]September 11 1
DD Month YY 1SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ‘ ‘ + DATENAME(MM, GETDATE()) + ‘ ‘ + RIGHT(CAST(YEAR(GETDATE()) AS VARCHAR(4)), 2) AS [DD Month YY]19 February 72 1
DD Month YYYY 1SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ‘ ‘ + DATENAME(MM, GETDATE()) + ‘ ‘ + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [DD Month YYYY]11 September 2002 1
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-YY 1SELECT REPLACE(RIGHT(CONVERT(VARCHAR(9), GETDATE(), 6), 6), ‘ ‘, ‘-‘) AS [Mon-YY]Sep-02 1
Mon-YYYY 1SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), GETDATE(), 106), 8), ‘ ‘, ‘-‘) AS [Mon-YYYY]Sep-2002 1
DD-Mon-YY 1SELECT REPLACE(CONVERT(VARCHAR(9), GETDATE(), 6), ‘ ‘, ‘-‘) AS [DD-Mon-YY]25-Dec-05 1
DD-Mon-YYYY 1SELECT REPLACE(CONVERT(VARCHAR(11), GETDATE(), 106), ‘ ‘, ‘-‘) AS [DD-Mon-YYYY]25-Dec-2005 1

1 To make the month name in upper case, simply use the UPPER string function.

Share

Recommended Posts

Leave a Reply

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