In this post, we want to discuss about efficient way to convert datetime format in SQL Server. Before we get started, if you want to know about split with any delimited string, please go through the following article: How to Split comma delimited string into a Table in SQL Server.

One of the most frequently asked questions in SQL Server forums is how to convert a datetime format in SQL into a specific date format. It is worth to note that the output of these date formats are of VARCHAR data types already and not of 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.

The SQL statements used below to return the different date formats use the SYSDATETIME() date function, which is new to SQL Server 2008.  The SYSDATETIME() function returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The SYSDATETIME() function used below can be replaced by the GETDATE() or GETUTCDATE() functions.

To make the datetime format in SQL results consistent, the date and time used to generate the sample output is June 8, 2011 1:30:45.9428675 PM.

Standard Date Formats
Date FormatStandardSQL StatementSample Output
Mon DD YYYY 1
HH:MIAM (or PM)
DefaultSELECT CONVERT(VARCHAR(20), SYSDATETIME(), 100)Jun 8 2011 1:30PM 1
MM/DD/YYUSASELECT CONVERT(VARCHAR(8), SYSDATETIME(), 1) AS [MM/DD/YY]06/08/11
MM/DD/YYYYUSASELECT CONVERT(VARCHAR(10), SYSDATETIME(), 101) AS [MM/DD/YYYY]06/08/2011
YY.MM.DDANSISELECT CONVERT(VARCHAR(8), SYSDATETIME(), 2) AS [YY.MM.DD]11.06.08
YYYY.MM.DDANSISELECT CONVERT(VARCHAR(10), SYSDATETIME(), 102) AS [YYYY.MM.DD]2011.06.08
DD/MM/YYBritish/FrenchSELECT CONVERT(VARCHAR(8), SYSDATETIME(), 3) AS [DD/MM/YY]08/06/11
DD/MM/YYYYBritish/FrenchSELECT CONVERT(VARCHAR(10), SYSDATETIME(), 103) AS [DD/MM/YYYY]08/06/2011
DD.MM.YYGermanSELECT CONVERT(VARCHAR(8), SYSDATETIME(), 4) AS [DD.MM.YY]08.06.11
DD.MM.YYYYGermanSELECT CONVERT(VARCHAR(10), SYSDATETIME(), 104) AS [DD.MM.YYYY]08.06.2011
DD-MM-YYItalianSELECT CONVERT(VARCHAR(8), SYSDATETIME(), 5) AS [DD-MM-YY]08-06-11
DD-MM-YYYYItalianSELECT CONVERT(VARCHAR(10), SYSDATETIME(), 105) AS [DD-MM-YYYY]08-06-2011
DD Mon YY 1SELECT CONVERT(VARCHAR(9), SYSDATETIME(), 6) AS [DD MON YY]08 Jun 11 1
DD Mon YYYY 1SELECT CONVERT(VARCHAR(11), SYSDATETIME(), 106) AS [DD MON YYYY]08 Jun 2011 1
Mon DD, YY 1SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 7) AS [Mon DD, YY]Jun 08, 11 1
Mon DD, YYYY 1SELECT CONVERT(VARCHAR(12), SYSDATETIME(), 107) AS [Mon DD, YYYY]Jun 08, 2011 1
HH:MM:SSSELECT CONVERT(VARCHAR(8), SYSDATETIME(), 8)
SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 108)
13:30:45
Mon D YYYY H:MI:SS.NNNNNNNAM (or PM) 1Default +

nanoseconds

SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 9)
SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 109)
Jun 8 2011 1:30:45.9428675PM 1
MM-DD-YYUSASELECT CONVERT(VARCHAR(8), SYSDATETIME(), 10) AS [MM-DD-YY]06-08-11
MM-DD-YYYYUSASELECT CONVERT(VARCHAR(10), SYSDATETIME(), 110) AS [MM-DD-YYYY]06-08-2011
YY/MM/DDSELECT CONVERT(VARCHAR(8), SYSDATETIME(), 11) AS [YY/MM/DD]11/06/08
YYYY/MM/DDSELECT CONVERT(VARCHAR(10), SYSDATETIME(), 111) AS [YYYY/MM/DD]2011/06/08
YYMMDDISOSELECT CONVERT(VARCHAR(6), SYSDATETIME(), 12) AS [YYMMDD]110608
YYYYMMDDISOSELECT CONVERT(VARCHAR(8), SYSDATETIME(), 112) AS [YYYYMMDD]20110608
DD Mon YYYY HH:MM:SS.NNNNNNN(24h) 1Europe default + nanosecondsSELECT CONVERT(VARCHAR(30), SYSDATETIME(), 13)
SELECT CONVERT(VARCHAR(30), SYSDATETIME(), 113)
08 Jun 2011 13:30:45.94286751
HH:MI:SS.NNNNNNN(24H)SELECT CONVERT(VARCHAR(16), SYSDATETIME(), 14) AS [HH:MI:SS:MMM(24H)] SELECT CONVERT(VARCHAR(16), SYSDATETIME(), 114) AS [HH:MI:SS:MMM(24H)]13:30:45.9428675
YYYY-MM-DD HH:MI:SS(24h)ODBC CanonicalSELECT CONVERT(VARCHAR(19), SYSDATETIME(), 120)2011-06-08 13:30:45
YYYY-MM-DD HH:MI:SS.NNNNNNN(24h)ODBC Canonical
(with nanoseconds)
SELECT CONVERT(VARCHAR(23), SYSDATETIME(), 121)2011-06-08 13:30:45.9428675
MM/DD/YY HH:MI:SS AMSELECT CONVERT(VARCHAR(20), SYSDATETIME(), 22)06/08/11 1:30:45 PM
YYYY-MM-DDSELECT CONVERT(VARCHAR(26), SYSDATETIME(), 23)2011-06-091
HH:MI:SS (24h)SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 24)13:30:45
YYYY-MM-DD HH:MI:SS.NNNNNNNSELECT CONVERT(VARCHAR(26), SYSDATETIME(), 25)2011-06-08 13:30:45.94286751
YYYY-MM-DDTHH:MM:SS:NNNNNNNISO8601SELECT CONVERT(VARCHAR(27), SYSDATETIME(), 126)2011-06-08T13:30:45.9428675
DD Mon YYYY HH:MI:SS.NNNNNNNAM 1KuwaitiSELECT CONVERT(VARCHAR(26), SYSDATETIME(), 130)08 Jun 2011 1:30:45.9428675PM1
DD/MM/YYYY HH:MI:SS.NNNNNNNAMKuwaitiSELECT CONVERT(VARCHAR(25), SYSDATETIME(), 131)08/06/2011 1:30:45.9428675PM

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

Standard Date Formats
Date FormatStandardSQL StatementSample Output
Mon DD YYYY 1
HH:MIAM (or PM)
DefaultSELECT CONVERT(VARCHAR(20), SYSDATETIME(), 100)Jun 8 2011 1:30PM 1
MM/DD/YYUSASELECT CONVERT(VARCHAR(8), SYSDATETIME(), 1) AS [MM/DD/YY]06/08/11
MM/DD/YYYYUSASELECT CONVERT(VARCHAR(10), SYSDATETIME(), 101) AS [MM/DD/YYYY]06/08/2011
YY.MM.DDANSISELECT CONVERT(VARCHAR(8), SYSDATETIME(), 2) AS [YY.MM.DD]11.06.08
YYYY.MM.DDANSISELECT CONVERT(VARCHAR(10), SYSDATETIME(), 102) AS [YYYY.MM.DD]2011.06.08
DD/MM/YYBritish/FrenchSELECT CONVERT(VARCHAR(8), SYSDATETIME(), 3) AS [DD/MM/YY]08/06/11
DD/MM/YYYYBritish/FrenchSELECT CONVERT(VARCHAR(10), SYSDATETIME(), 103) AS [DD/MM/YYYY]08/06/2011
DD.MM.YYGermanSELECT CONVERT(VARCHAR(8), SYSDATETIME(), 4) AS [DD.MM.YY]08.06.11
DD.MM.YYYYGermanSELECT CONVERT(VARCHAR(10), SYSDATETIME(), 104) AS [DD.MM.YYYY]08.06.2011
DD-MM-YYItalianSELECT CONVERT(VARCHAR(8), SYSDATETIME(), 5) AS [DD-MM-YY]08-06-11
DD-MM-YYYYItalianSELECT CONVERT(VARCHAR(10), SYSDATETIME(), 105) AS [DD-MM-YYYY]08-06-2011
DD Mon YY 1SELECT CONVERT(VARCHAR(9), SYSDATETIME(), 6) AS [DD MON YY]08 Jun 11 1
DD Mon YYYY 1SELECT CONVERT(VARCHAR(11), SYSDATETIME(), 106) AS [DD MON YYYY]08 Jun 2011 1
Mon DD, YY 1SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 7) AS [Mon DD, YY]Jun 08, 11 1
Mon DD, YYYY 1SELECT CONVERT(VARCHAR(12), SYSDATETIME(), 107) AS [Mon DD, YYYY]Jun 08, 2011 1
HH:MM:SSSELECT CONVERT(VARCHAR(8), SYSDATETIME(), 8)
SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 108)
13:30:45
Mon D YYYY H:MI:SS.NNNNNNNAM (or PM) 1Default +

nanoseconds

SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 9)
SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 109)
Jun 8 2011 1:30:45.9428675PM 1
MM-DD-YYUSASELECT CONVERT(VARCHAR(8), SYSDATETIME(), 10) AS [MM-DD-YY]06-08-11
MM-DD-YYYYUSASELECT CONVERT(VARCHAR(10), SYSDATETIME(), 110) AS [MM-DD-YYYY]06-08-2011
YY/MM/DDSELECT CONVERT(VARCHAR(8), SYSDATETIME(), 11) AS [YY/MM/DD]11/06/08
YYYY/MM/DDSELECT CONVERT(VARCHAR(10), SYSDATETIME(), 111) AS [YYYY/MM/DD]2011/06/08
YYMMDDISOSELECT CONVERT(VARCHAR(6), SYSDATETIME(), 12) AS [YYMMDD]110608
YYYYMMDDISOSELECT CONVERT(VARCHAR(8), SYSDATETIME(), 112) AS [YYYYMMDD]20110608
DD Mon YYYY HH:MM:SS.NNNNNNN(24h) 1Europe default + nanosecondsSELECT CONVERT(VARCHAR(30), SYSDATETIME(), 13)
SELECT CONVERT(VARCHAR(30), SYSDATETIME(), 113)
08 Jun 2011 13:30:45.94286751
HH:MI:SS.NNNNNNN(24H)SELECT CONVERT(VARCHAR(16), SYSDATETIME(), 14) AS [HH:MI:SS:MMM(24H)] SELECT CONVERT(VARCHAR(16), SYSDATETIME(), 114) AS [HH:MI:SS:MMM(24H)]13:30:45.9428675
YYYY-MM-DD HH:MI:SS(24h)ODBC CanonicalSELECT CONVERT(VARCHAR(19), SYSDATETIME(), 120)2011-06-08 13:30:45
YYYY-MM-DD HH:MI:SS.NNNNNNN(24h)ODBC Canonical
(with nanoseconds)
SELECT CONVERT(VARCHAR(23), SYSDATETIME(), 121)2011-06-08 13:30:45.9428675
MM/DD/YY HH:MI:SS AMSELECT CONVERT(VARCHAR(20), SYSDATETIME(), 22)06/08/11 1:30:45 PM
YYYY-MM-DDSELECT CONVERT(VARCHAR(26), SYSDATETIME(), 23)2011-06-091
HH:MI:SS (24h)SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 24)13:30:45
YYYY-MM-DD HH:MI:SS.NNNNNNNSELECT CONVERT(VARCHAR(26), SYSDATETIME(), 25)2011-06-08 13:30:45.94286751
YYYY-MM-DDTHH:MM:SS:NNNNNNNISO8601SELECT CONVERT(VARCHAR(27), SYSDATETIME(), 126)2011-06-08T13:30:45.9428675
DD Mon YYYY HH:MI:SS.NNNNNNNAM 1KuwaitiSELECT CONVERT(VARCHAR(26), SYSDATETIME(), 130)08 Jun 2011 1:30:45.9428675PM1
DD/MM/YYYY HH:MI:SS.NNNNNNNAMKuwaitiSELECT CONVERT(VARCHAR(25), SYSDATETIME(), 131)08/06/2011 1:30:45.9428675PM

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

Datetime format in sql server

Leave a Comment