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 is 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.
Bonus
If you want to convert datetime as same as “19-Jun-2022 10:15:23 AM”, you have to create a Scalar-valued Function and convert datetime format using SELECT dbo.fn_FormatDate(GETDATE()). I like to share the fuction as following:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
CREATE FUNCTION [dbo].[fn_FormatDate] ( @Date DATETIME ) RETURNS VARCHAR(50) AS BEGIN DECLARE @Return AS VARCHAR(50), @FormatDate AS VARCHAR(50), @FormatTime AS VARCHAR(50) SELECT @FormatDate=REPLACE(CONVERT(VARCHAR, @Date, 106),' ','-') SELECT @FormatTime=SUBSTRING(CONVERT(VARCHAR(20), @Date, 9), 13, 8) + ' ' + SUBSTRING(CONVERT(VARCHAR(30), @Date, 9), 25, 2) SET @Return=@FormatDate+' '+@FormatTime Return @Return END --GO --SELECT dbo.fn_FormatDate(GETDATE()) |
Datetime format in SQL server
The article was published on November 6, 2016 @ 12:53 PM
Leave a Comment