SQL Server Portal

Datetime conversion from one format to another format is sometimes very critical. You should be very careful while doing it, since even a single digit can mess up everything. Recently, I was working on a Report and I needed to convert datetime to a short date format in SQL Server. Given below are three different methods we can use to achieve it.

Method 1 :
In this method, we will convert the datetime value to short date format, using CONVERT function. Here goes the script.

--This script is compatible with SQL Server 2005 and above.
DECLARE @datetime DATETIME
SET @datetime = GETDATE()

SELECT @datetime AS [DATE TIME],
CONVERT(VARCHAR(11), @datetime, 100) AS [SHORT DATE TIME]
GO
--OUTPUT

Method 2 :
In this method, we will convert the datetime to short date format using CAST function. Script given below.

--This script is compatible with SQL Server 2005 and above.
DECLARE @datetime DATETIME
SET @datetime = GETDATE()

SELECT @datetime AS [DATE TIME],
CAST(@datetime AS VARCHAR(11)) AS [SHORT DATE TIME]
GO
--OUTPUT

Method 3 :
In this method, we will convert the datetime to short date format using FORMAT function. Script as follows:

--This script is compatible with SQL Server 2012 and above.
DECLARE @datetime DATETIME
SET @datetime = GETDATE()

SELECT @datetime AS [DATE TIME],
FORMAT(@datetime, 'MMM dd yyyy') AS [SHORT DATE TIME]
GO
--OUTPUT

Conclusion :
As you can see, we used three different methods (CONVERT, CAST & FORMAT functions)  to convert datetime to short date format and all of them gave the same result set. Nonetheless, I usually use Method 1 (CONVERT function) because it is compatible with different versions of SQL SERVER and also the performance is better than other two.

What do you prefer? Do write to me.