Link to home
Start Free TrialLog in
Avatar of jxharding
jxharding

asked on

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!
Avatar of jxharding
jxharding

ASKER

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-

Avatar of Aneesh
Hi jxharding,

What's the datatype of the field   Sales.Sales

Aneesh
hi, it is of type float
declare @x float
set @x = 6567218.2400000021

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

select convert(nvarchar(100), myvalue) + case myvalue < 0 then '-' end as my_converted_value
i think casting to a varchar without a size will result in bugged results.
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
Hi,
i tried  select convert(nvarchar(100), myvalue) + case myvalue < 0 then '-' end as my_converted_value
but i still get
6.56722e+006
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
hI

Did you try my post ?

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


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

Hi,
hope this helps
-----------------
select cast(cast(round(sum(sales.sales),2) as numeric(18,2)) as varchar) as sales from sales
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?
you probably should be doing this in the frontend, but okay,

select myvalue +
   case myvalue < 0 then '-' end as my_converted_value
ASKER CERTIFIED SOLUTION
Avatar of imran_fast
imran_fast

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
thank you all!!!!!!!!