converting to a float

Question

Post reply

converting to a float

Alex Thomas

Ten Centuries

Points: 1120
More actions
July 21, 2005 at 12:44 pm

#66738

hi, im trying to use a function like the convert(float, x) function but i need to convert a rather long number to number with the scale (think it's scale ) of 2

ie - 3434.3484365657

becomes 3434.35

any pointers? should i use cast?

thanks!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246944 More actions · Answer 1

Try running this and see if it gets you moving in the right direction:

declare @a float

declare @b-2 decimal(19,2)

set @a = 3434.3484365657

set @b-2 = @a

select @a Float, @b-2 TwoDPs

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Frank Kalis SSC Guru Points: 111183 More actions · Answer 2

should i use cast?

Why don't you try it out?

To explain a bit. This behaviour is "by design". What happens is that the conversion from FLOAT to VARCHAR uses internally something like CONVERT(VARCHAR, < value >, 0) which might be a legacy problem from those days of only single-precision float types available. SQL Server can represent double-precision float when you use DECLARE ... FLOAT(53) There is however a workaround available, that doesn't need a conversion to another data type.

DECLARE @test-2 FLOAT

SELECT @test-2 = 3434.3484365657

SELECT @test-2, LTRIM(RTRIM(STR(ROUND(@test,10),15,10)))

----------------------------------------------------- ---------------

3434.3484365657 3434.3484365657

(1 row(s) affected)

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

Ignore my posting and go with Phil's suggestion. Misread your question. I thought you were wondering why the conversion from FLOAT to VARCHAR cuts off significant decimal places.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Ray M SSC-Insane Points: 21093 More actions · Answer 4

I guess its my curious nature, or the urge to help out, But why do you want to use float?

Float is termed an approximate datatype.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_con_03_6mht.asp

Read this for more information.

As noted most of the time the approximation of the value doesn't matter, but sometimes it does. If it does, or it Might matter, then go with decimal, or numeric.

For example check out this funk

declare @a float

declare @b-2 numeric (19,2)

set @a = 3434.33333333333

set @b-2 = @a

select @a Float, @b-2 TwoDPs

FloatTwoDPs

3434.3333333333298,3434.33

Alex Thomas Ten Centuries Points: 1120 More actions · Answer 5

hi guys,

thanks for the responses..

im happyto use anything really, not nec a float. i needto convert a long number such as 12.8634456776 to something that resembles currency (covnerting to 'money' has 4 decimal places), so i thought a float with 2 decimal places would work.

it's in a reporting tool, so i can't declare variables, id haveto use a straight function.

Phil Parkin SSC Guru Points: 246944 More actions · Answer 6

Not sure whether you've already worked this out, but sounds like you need

select cast(3434.3484365657 as decimal(19,2))

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Alex Thomas Ten Centuries Points: 1120 More actions · Answer 7

that's perfect Phil, many thanks

i got as far as doing using round,2 which gave me 3434.350000000000 but then couldn't get any further.