SQL Server Portal

Data presentation is one of the aspects that is much more important when you need to present a report to end user. Usually, I recommend that you should do it at the presentation layer (front end). However, sometimes we need to do all the formatting at database level itself and just present it at presentation level. One of the most frequently formattings we usually come across is datetime data type formatting and to format datetime we need to convert it into varchar data type.

Given below are the solutions.

Solution 1 :
In this solution, we need to use the Traditional method using CONVERT function.

USE AdventureWorks2012
GO

SELECT PurchaseOrderID
, OrderDate
, CONVERT(VARCHAR(11),OrderDate,113)
AS [OrderDate Using Convert]
FROM Purchasing.PurchaseOrderHeader
GO
--OUTPUT

Solution 2 :
In this solution, we need to use one of the new conversion functions shipped with SQL Server 2012 namely TRY_CONVERT function.

USE AdventureWorks2012
GO

SELECT PurchaseOrderID
, OrderDate
, TRY_CONVERT(VARCHAR(11),OrderDate,113)
AS [OrderDate Using Try_Convert]
FROM Purchasing.PurchaseOrderHeader
GO
--OUTPUT

Solution 3 :
In this solution, we need to use one of the new formatting functions shipped with SQL Server 2012 namely FORMAT function.
I usually recommend this method because it gives you variety for formatting.

USE AdventureWorks2012
GO

SELECT PurchaseOrderID
, OrderDate
, FORMAT(OrderDate,'dd MMM yyyy')
AS [OrderDate Using Format]
FROM Purchasing.PurchaseOrderHeader
GO
--OUTPUT