In this post, we want to discuss the 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 noting that the output of these date formats are of VARCHAR data types already and not of the 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.

Date Format Standard SQL Statement Sample Output
Mon DD YYYY 1
HH:MIAM (or PM)
Default SELECT CONVERT(VARCHAR(20), SYSDATETIME(), 100) Jun 8 2011 1:30PM 1
MM/DD/YY USA SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 1) AS [MM/DD/YY] 06/08/11
MM/DD/YYYY USA SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 101) AS [MM/DD/YYYY] 06/08/2011
YY.MM.DD ANSI SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 2) AS [YY.MM.DD] 11.06.08
YYYY.MM.DD ANSI SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 102) AS [YYYY.MM.DD] 2011.06.08
DD/MM/YY British
/French
SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 3) AS [DD/MM/YY] 08/06/11
DD/MM/YYYY British
/French
SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 103) AS [DD/MM/YYYY] 08/06/2011
DD.MM.YY German SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 4) AS [DD.MM.YY] 08.06.11
DD.MM.YYYY German SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 104) AS [DD.MM.YYYY] 08.06.2011
DD-MM-YY Italian SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 5) AS [DD-MM-YY] 08-06-11
DD-MM-YYYY Italian SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 105) AS [DD-MM-YYYY] 08-06-2011
DD Mon YY 1 SELECT CONVERT(VARCHAR(9), SYSDATETIME(), 6) AS [DD MON YY] 08 Jun 11 1
DD Mon YYYY 1 SELECT CONVERT(VARCHAR(11), SYSDATETIME(), 106) AS [DD MON YYYY] 08 Jun 2011 1
Mon DD, YY 1 SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 7) AS [Mon DD, YY] Jun 08, 11 1
Mon DD, YYYY 1 SELECT CONVERT(VARCHAR(12), SYSDATETIME(), 107) AS [Mon DD, YYYY] Jun 08, 2011 1
HH:MM:SS SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 8)
SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 108)
13:30:45
Mon D YYYY H:MI:SS.NNNNNNNAM (or PM) 1

Default +

nanoseconds

SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 9)
SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 109)
Jun 8 2011 1:30:45.9428675PM 1
MM-DD-YY USA SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 10) AS [MM-DD-YY] 06-08-11
MM-DD-YYYY USA SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 110) AS [MM-DD-YYYY] 06-08-2011
YY/MM/DD SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 11) AS [YY/MM/DD] 11/06/08
YYYY/MM/DD SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 111) AS [YYYY/MM/DD] 2011/06/08
YYMMDD ISO SELECT CONVERT(VARCHAR(6), SYSDATETIME(), 12) AS [YYMMDD] 110608
YYYYMMDD ISO SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 112) AS [YYYYMMDD] 20110608
DD Mon YYYY HH:MM:SS.NNNNNNN(24h) 1 Europe default + nanoseconds SELECT 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 Canonical SELECT 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 AM SELECT CONVERT(VARCHAR(20), SYSDATETIME(), 22) 06/08/11 1:30:45 PM
YYYY-MM-DD SELECT 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.NNNNNNN SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 25) 2011-06-08 13:30:45.94286751
YYYY-MM-DDTHH:MM:SS:NNNNNNN ISO8601 SELECT CONVERT(VARCHAR(27), SYSDATETIME(), 126) 2011-06-08T
13:30:45.9428675
DD Mon YYYY HH:MI:SS.NNNNNNNAM 1 Kuwaiti SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 130) 08 Jun 2011 1:30:45.9428675PM1
DD/MM/YYYY HH:MI:SS.NNNNNNNAM Kuwaiti SELECT CONVERT(VARCHAR(25), SYSDATETIME(), 131) 08/06/2011 1:30:45.9428675PM

 

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

Date Format Standard SQL Statement Sample Output
Mon DD YYYY 1
HH:MIAM (or PM)
Default SELECT CONVERT(VARCHAR(20), SYSDATETIME(), 100) Jun 8 2011 1:30PM 1
MM/DD/YY USA SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 1) AS [MM/DD/YY] 06/08/11
MM/DD/YYYY USA SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 101) AS [MM/DD/YYYY] 06/08/2011
YY.MM.DD ANSI SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 2) AS [YY.MM.DD] 11.06.08
YYYY.MM.DD ANSI SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 102) AS [YYYY.MM.DD] 2011.06.08
DD/MM/YY British
/French
SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 3) AS [DD/MM/YY] 08/06/11
DD/MM/YYYY British
/French
SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 103) AS [DD/MM/YYYY] 08/06/2011
DD.MM.YY German SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 4) AS [DD.MM.YY] 08.06.11
DD.MM.YYYY German SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 104) AS [DD.MM.YYYY] 08.06.2011
DD-MM-YY Italian SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 5) AS [DD-MM-YY] 08-06-11
DD-MM-YYYY Italian SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 105) AS [DD-MM-YYYY] 08-06-2011
DD Mon YY 1 SELECT CONVERT(VARCHAR(9), SYSDATETIME(), 6) AS [DD MON YY] 08 Jun 11 1
DD Mon YYYY 1 SELECT CONVERT(VARCHAR(11), SYSDATETIME(), 106) AS [DD MON YYYY] 08 Jun 2011 1
Mon DD, YY 1 SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 7) AS [Mon DD, YY] Jun 08, 11 1
Mon DD, YYYY 1 SELECT CONVERT(VARCHAR(12), SYSDATETIME(), 107) AS [Mon DD, YYYY] Jun 08, 2011 1
HH:MM:SS SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 8)
SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 108)
13:30:45
Mon D YYYY H:MI:SS.NNNNNNNAM (or PM) 1

Default +

nanoseconds

SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 9)
SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 109)
Jun 8 2011 1:30:45.9428675PM 1
MM-DD-YY USA SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 10) AS [MM-DD-YY] 06-08-11
MM-DD-YYYY USA SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 110) AS [MM-DD-YYYY] 06-08-2011
YY/MM/DD SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 11) AS [YY/MM/DD] 11/06/08
YYYY/MM/DD SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 111) AS [YYYY/MM/DD] 2011/06/08
YYMMDD ISO SELECT CONVERT(VARCHAR(6), SYSDATETIME(), 12) AS [YYMMDD] 110608
YYYYMMDD ISO SELECT CONVERT(VARCHAR(8), SYSDATETIME(), 112) AS [YYYYMMDD] 20110608
DD Mon YYYY HH:MM:SS.NNNNNNN(24h) 1 Europe default + nanoseconds SELECT 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 Canonical SELECT 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 AM SELECT CONVERT(VARCHAR(20), SYSDATETIME(), 22) 06/08/11 1:30:45 PM
YYYY-MM-DD SELECT 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.NNNNNNN SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 25) 2011-06-08 13:30:45.94286751
YYYY-MM-DDTHH:MM:SS:NNNNNNN ISO8601 SELECT CONVERT(VARCHAR(27), SYSDATETIME(), 126) 2011-06-08T
13:30:45.9428675
DD Mon YYYY HH:MI:SS.NNNNNNNAM 1 Kuwaiti SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 130) 08 Jun 2011 1:30:45.9428675PM1
DD/MM/YYYY HH:MI:SS.NNNNNNNAM Kuwaiti SELECT 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