Thursday, January 13, 2011

Convert VarChar to Currency in SQL Server

To convert a varchar to currency, the recommended practice is to do the formatting in the front end.
However if that option is not available to you, you can use the following T-SQL code to do the formatting. In the code shown below, we are converting varchar into US Dollar currency.
DECLARE @t TABLE(amount decimal(12,2)) INSERT INTO @t SELECT 27450 union all SELECT 2841.2 union all SELECT 8786723.62 union all SELECT 8723SELECT amount,'$'+ CONVERT(varchar(100),CAST(amount as money),1) as converted_amountFROM @t

Note : The value must be converted to money datatype before the formatting

No comments:

Post a Comment