cast values over a million to char/varchar/nvarchar - it shows: 6.56722e+006

hi, i need to cast a value over a million to nvarchar

this is my query:
SELECT     cast(cast(round(SUM(Sales.Sales),2) as float) as char) AS Sales   from Sales
resulting in 6.56722e+006  



I need to display it properly,if i take away the casts i get  6567218.2400000021
is it all these extra zero's causing the trouble ?
i tried char,nvarchar and varchar, but i cant get the result

thanks!
jxhardingAsked:
Who is Participating?
 
imran_fastConnect With a Mentor Commented:
select
      cast(cast(round(sum(sales.sales),2) as numeric(18,2)) as varchar) as sales ,
      cast(cast(round(sum(sales.costs),2) as numeric(18,2)) as varchar) +'-' as costs

from sales
0
 
jxhardingAuthor Commented:
the thing is that i need to add the sign at the end of the value if it is a negative value
 e.g. 589653.23-

0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Hi jxharding,

What's the datatype of the field   Sales.Sales

Aneesh
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
jxhardingAuthor Commented:
hi, it is of type float
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
declare @x float
set @x = 6567218.2400000021

select CAST(cast(@x as numeric(18,9)) as varchar)
0
 
lluthienCommented:
try this:

select convert(nvarchar(100), myvalue) + case myvalue < 0 then '-' end as my_converted_value
0
 
lluthienCommented:
i think casting to a varchar without a size will result in bugged results.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
lluthien,
> i think casting to a varchar without a size will result in bugged results.

By default it will be equivalent to varchar(30). so it wont rise any error
0
 
jxhardingAuthor Commented:
Hi,
i tried  select convert(nvarchar(100), myvalue) + case myvalue < 0 then '-' end as my_converted_value
but i still get
6.56722e+006
0
 
lluthienCommented:
ah..
i missed the post with the float.

you'd have to do aneesh's trick as well..

convert it to a decimal, then append the minus with my case part
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
hI

Did you try my post ?

select CAST(cast(myvalue as numeric(18,9)) as varchar)


Aneesh
0
 
imran_fastCommented:
try this
====
SELECT     cast(round(SUM(Sales.Sales),2)as varchar)  AS Sales   from Sales

0
 
imran_fastCommented:
Hi,
hope this helps
-----------------
select cast(cast(round(sum(sales.sales),2) as numeric(18,2)) as varchar) as sales from sales
0
 
jxhardingAuthor Commented:
thank you all
the thing now is that i need to add a - at the back of some of these statements.
e.g. sales will be
Sales 123456
Costs 5865.5-
can i bring this in?
0
 
lluthienCommented:
you probably should be doing this in the frontend, but okay,

select myvalue +
   case myvalue < 0 then '-' end as my_converted_value
0
 
jxhardingAuthor Commented:
thank you all!!!!!!!!
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.

All Courses

From novice to tech pro — start learning today.