• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 15430
  • 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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