Efficient Way To Convert Datetime Format In SQL

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 Statement s 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 ¹ HH:MIAM (or PM)	Default	SELECT CONVERT(VARCHAR(20), SYSDATETIME(), 100)	Jun 8 2011 1:30PM ¹
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 ¹	–	SELECT CONVERT(VARCHAR(9), SYSDATETIME(), 6) AS [DD MON YY]	08 Jun 11 ¹
DD Mon YYYY ¹	–	SELECT CONVERT(VARCHAR(11), SYSDATETIME(), 106) AS [DD MON YYYY]	08 Jun 2011 ¹
Mon DD, YY ¹	–	SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 7) AS [Mon DD, YY]	Jun 08, 11 ¹
Mon DD, YYYY ¹	–	SELECT CONVERT(VARCHAR(12), SYSDATETIME(), 107) AS [Mon DD, YYYY]	Jun 08, 2011 ¹
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) ¹	Default + nanoseconds	SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 9) SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 109)	Jun 8 2011 1:30:45.9428675PM ¹
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) ¹	Europe default + nanoseconds	SELECT CONVERT(VARCHAR(30), SYSDATETIME(), 13) SELECT CONVERT(VARCHAR(30), SYSDATETIME(), 113)	08 Jun 2011 13:30:45.9428675¹
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-09¹
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.9428675¹
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 ¹	Kuwaiti	SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 130)	08 Jun 2011 1:30:45.9428675PM¹
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 ¹ HH:MIAM (or PM)	Default	SELECT CONVERT(VARCHAR(20), SYSDATETIME(), 100)	Jun 8 2011 1:30PM ¹
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 ¹	–	SELECT CONVERT(VARCHAR(9), SYSDATETIME(), 6) AS [DD MON YY]	08 Jun 11 ¹
DD Mon YYYY ¹	–	SELECT CONVERT(VARCHAR(11), SYSDATETIME(), 106) AS [DD MON YYYY]	08 Jun 2011 ¹
Mon DD, YY ¹	–	SELECT CONVERT(VARCHAR(10), SYSDATETIME(), 7) AS [Mon DD, YY]	Jun 08, 11 ¹
Mon DD, YYYY ¹	–	SELECT CONVERT(VARCHAR(12), SYSDATETIME(), 107) AS [Mon DD, YYYY]	Jun 08, 2011 ¹
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) ¹	Default + nanoseconds	SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 9) SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 109)	Jun 8 2011 1:30:45.9428675PM ¹
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) ¹	Europe default + nanoseconds	SELECT CONVERT(VARCHAR(30), SYSDATETIME(), 13) SELECT CONVERT(VARCHAR(30), SYSDATETIME(), 113)	08 Jun 2011 13:30:45.9428675¹
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-09¹
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.9428675¹
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 ¹	Kuwaiti	SELECT CONVERT(VARCHAR(26), SYSDATETIME(), 130)	08 Jun 2011 1:30:45.9428675PM¹
DD/MM/YYYY HH:MI:SS.NNNNNNNAM	Kuwaiti	SELECT CONVERT(VARCHAR(25), SYSDATETIME(), 131)	08/06/2011 1:30:45.9428675PM

¹ 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:


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())