Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 15340
  • Last Modified:

How to convert decimal to varchar

Select OrderID,
'Sell--' + Convert(varchar(24), sum(CAST(Price AS DECIMAL(13,2))) )
From OrderDetail
GroupBy OrderID

I want to display price as varchar but somehow above code send an following error
"Error converting data type varchar to numeric."

I want to display result as following
OrderID Price
-----------------
1, S-212.23
2, S-46.83
3, S-912.20
4, S-52.73
0
Bharat Guru
Asked:
Bharat Guru
  • 2
1 Solution
 
LowfatspreadCommented:
try
sounds like you've got some invalid data... what is the datatype of the price column?

 Select OrderID,
'Sell--' +Convert(varchar(24),sum(case when isnumeric(price) when 1 then convert(decimal(13,2),Price))
                                 else 0.00 end))
 From OrderDetail
GroupBy OrderID

and use this to identify any non numerics..
select orderid , price
from orderdetail
where isnumeric(price) = 0
0
 
Bharat GuruAuthor Commented:
I actually want to do something like following

Declare @Temp CHAR (1)
Set @Temp = 'Y'

Select OrderID,
              Case When @Temp = 'Y' Then
                  Convert(varchar(24), sum(CAST(Price AS DECIMAL(13,2)))  )
              Else
                  sum(CAST(Price AS DECIMAL(13,2)))
              End AS "Price"

 From OrderDetail
GroupBy OrderID


Price is a varchar field in table
0
 
LowfatspreadCommented:
you can only do this...
you can't have multiple datatypes for a column....

Declare @Temp CHAR (1)
Set @Temp = 'Y'

Select OrderID,
              Case When @Temp = 'Y' Then
                  Convert(varchar(24), price)
              Else
                  convert(varchar(13),Price)
              End AS "Price"
 From ( select orderid,sum(cast(price as decimal(13,2))) as  price from OrderDetail
GroupBy OrderID) as x


you could use dynamic sql (or have 2 sql statements (preferred solution))

declare @sqlstm varchar(8000)
Set @sqlstm='Select orderid,' + case when @temp='Y' then 'Convert(varchar(24), sum(CAST(Price AS DECIMAL(13,2)))  ) ' else ' sum(CAST(Price AS DECIMAL(13,2)))' end + ' from orderdetail group by orderid'

exec(@sqlstm)  


0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now