Convert Decimal to Varchar

Question

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.

Answer 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 

Answer 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' )