[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

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!
0
jxharding
Asked:
jxharding
  • 5
  • 4
  • 4
  • +1
1 Solution
 
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
 
jxhardingAuthor Commented:
hi, it is of type float
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
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
 
imran_fastCommented:
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:
thank you all!!!!!!!!
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.

  • 5
  • 4
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now