Feeds:
Posts
Comments

Posts Tagged ‘CONVERT’

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

datetimetovarchar.1.1

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

datetimetovarchar.1.2

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

datetimetovarchar.1.3

Advertisements

Read Full Post »

Recently, I came across a query in a blog, ‘How to remove leading zeros after a decimal point’. I then started developing the shortest possible solution and thought of sharing with my blog readers. Let me create a sample to demonstrate the solution.

DECLARE @tbl_sample AS TABLE
(
 [ID] INT,
 [Col_Varchar] VARCHAR(50)
)

INSERT INTO @tbl_sample VALUES (1,'2013.0000000001')
INSERT INTO @tbl_sample VALUES (2,'2014.0000000002')
INSERT INTO @tbl_sample VALUES (3,'2015.0000000003')
INSERT INTO @tbl_sample VALUES (4,'2016.0000000044')
INSERT INTO @tbl_sample VALUES (5,'2017.0000000555')

SELECT * FROM @tbl_sample
 --OUTPUT

remove leading zero.1.1

SOLUTION 1 :
Given below is the solution using PARSENAME & CONCAT function.

--This script is compatible with SQL Server 2012 and above.
SELECT [ID]
,CONCAT(PARSENAME([Col_Varchar],2)
,'.',CONVERT(INT,PARSENAME([Col_Varchar],1)))
AS [Col_Varchar]
FROM @tbl_sample
 --OUTPUT

remove leading zero.1.2

SOLUTION 2 :
Given below is the solution using PARSENAME & CONVERT function.

--This script is compatible with SQL Server 2005 and above.
SELECT
[ID]
,CONVERT(VARCHAR(5),PARSENAME([Col_Varchar],2))
+ '.'
+ CONVERT(VARCHAR(5),CONVERT(INT,PARSENAME([Col_Varchar],1)))
AS [Col_Varchar]
FROM @tbl_sample
 --OUTPUT

remove leading zero.1.3

Read Full Post »