'Convert Decimal to Varchar

I have a decimal column in a table defined as decimal(8,3). I would like to include this column in a Select statement, convert it to a Varchar and only display two decimal places. I can't seem to find the right combination of options to do this because everything I try still produces three decimal places.

sql-servertsql


Solution 1:[1]

Here's one way:

create table #work
(
  something decimal(8,3) not null 
)

insert #work values ( 0 )
insert #work values ( 12345.6789 )
insert #work values ( 3.1415926 )
insert #work values ( 45 )
insert #work values ( 9876.123456 )
insert #work values ( -12.5678 )

select convert(varchar,convert(decimal(8,2),something))
from #work

if you want it right-aligned, something like this should do you:

select str(something,8,2) from #work

Solution 2:[2]

If you are using SQL Server 2012, 2014 or newer, use the Format Function instead:

select Format( decimalColumnName ,'FormatString','en-US' )

Review the Microsoft topic and .NET format syntax for how to define the format string.

An example for this question would be:

select Format( MyDecimalColumn ,'N','en-US' )

Solution 3:[3]

You might need to convert the decimal to money (or decimal(8,2)) to get that exact formatting. The convert method can take a third parameter that controls the formatting style:

convert(varchar, cast(price as money))       12345.67
convert(varchar, cast(price as money), 0)    12345.67
convert(varchar, cast(price as money), 1)    12,345.67

Solution 4:[4]

I think CAST(ROUND(yourColumn,2) as varchar) should do the job.

But why do you want to do this presentational formatting in T-SQL?

Solution 5:[5]

Hope this will help you

Cast(columnName as Numeric(10,2)) 
        or

Cast(@s as decimal(10,2))

I am not getting why you want to cast to varchar?.If you cast to varchar again convert back to decimail for two decimal points

Solution 6:[6]

Hope this will help .

DECLARE  @emp_cond nvarchar(Max) =' ',@LOCATION_ID  NUMERIC(18,0)   
SET@LOCATION_ID=10110000000
IF CAST(@LOCATION_ID AS VARCHAR(18))<>' ' 
            BEGIN
                SELECT @emp_cond= @emp_cond + N' AND 
CM.STATIC_EMP_INFO.EMP_ID = ' ' '+  CAST(@LOCATION_ID AS VARCHAR(18))  +' ' ' '
           END
print  @emp_cond  
EXEC( @emp_cond)

Solution 7:[7]

If the type is decimal(18,0) you need to do a double convert like so:

convert(varchar, convert(decimal, [Bill to Account Number] ))

Otherwise, if you just do convert(varchar) you will get scientific notation.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Nicholas Carey
Solution 2 Paul
Solution 3 Guffa
Solution 4
Solution 5
Solution 6 Benoît Latinier
Solution 7 user890332

Related Questions