Try specifying the desired precision and scale on your decimal/numeric
declaration:
SELECT CONVERT(varchar(100), CAST(@testFloat AS decimal(38,2)))
--
Hope this helps.
Dan Guzman
SQL Server MVP
"David Sharp" <da**@daveandcaz.freeserve.co.uk> wrote in message
news:ca*************************@posting.google.co m...
I'm trying to find a way to format a FLOAT variable into a varchar in
SQL Server 2000 but using CAST/CONVERT I can only get scientific
notation i.e. 1e+006 instead of 1000000 which isn't really what I
wanted.
Preferably the varchar would display the number to 2 decimal places
but I'd settle for integers only as this conversion isn't business
critical and is a nice to have for background information.
Casting to MONEY or NUMERIC before converting to a varchar works fine
for most cases but of course runs the risk of arithmetic overflow if
the FLOAT value is too precise for MONEY/NUMERIC to handle. If anyone
knows of an easy way to test whether overflow will occur and therefore
to know not to convert it then that would be an option.
I appreciate SQL Server isn't great at formatting and it would be far
easier in the client code but code this is being performed as a
description of a very simple calculation in a trigger, all stored to
the database on the server side so there's no opportunity for client
intervention.
Example code:
declare @testFloat float
select @testFloat = 1000000.12
select convert(varchar(100),@testFloat) -- gives 1e+006
select cast(@testFloat as varchar(100)) -- gives 1e+006
select convert(varchar(100),cast(@testFloat as money)) -- gives
1000000.12
select @testFloat = 12345678905345633453453624453453524.123
select convert(varchar(100),cast(@testFloat as money)) -- gives
arithmetic overflow error
select convert(varchar(100),cast(@testFloat as numeric)) -- gives
arithmetic overflow error
Any suggestions welcome...
Cheers
Dave
