Thursday, June 06, 2013

Convert Money to Varchar using SQL

Hi,

When we try to convert Money variable to Varchar in SQL it automatically round off the value to 2 decimal places.

Following is example

DECLARE @UnitCost MONEY

SET @UnitCost = .0167

SELECT
   @UnitCost,
   CAST(@UnitCost AS VARCHAR(30)),
   CAST(@UnitCost AS DECIMAL(30, 4)),
   CAST(CAST(@UnitCost AS DECIMAL(30, 4)) AS VARCHAR(30))


but if you want value to be rounded off upto 4 decimal places then please see the following code

DECLARE @UnitCost MONEY

SET @UnitCost = .016789

SELECT
    @UnitCost, convert(varchar(10),@unitcost,2),