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!
this is my query:
SELECT cast(cast(round(SUM(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!
Hi jxharding,
What's the datatype of the field Sales.Sales
Aneesh
What's the datatype of the field Sales.Sales
Aneesh
ASKER
hi, it is of type float
declare @x float
set @x = 6567218.2400000021
select CAST(cast(@x as numeric(18,9)) as varchar)
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
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
> 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
ASKER
Hi,
i tried select convert(nvarchar(100), myvalue) + case myvalue < 0 then '-' end as my_converted_value
but i still get
6.56722e+006
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
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
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
====
SELECT cast(round(SUM(Sales.Sales
Hi,
hope this helps
-----------------
select cast(cast(round(sum(sales. sales),2) as numeric(18,2)) as varchar) as sales from sales
hope this helps
-----------------
select cast(cast(round(sum(sales.
ASKER
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?
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
select myvalue +
case myvalue < 0 then '-' end as my_converted_value
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
thank you all!!!!!!!!
ASKER
e.g. 589653.23-