CAST : chopping digits from a FLOAT type

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

CAST : chopping digits from a FLOAT type

Author

Topic

nmarks
Yak Posting Veteran

53 Posts

Posted - 2008-01-06 : 05:46:22

Hi,

I am having trouble converting a floating point number to a string. When I use the CAST function the string I'm given has only the first six digits. I need to be working to high precision. Can anyone help on this:

This example will show you what I mean:

declare @a FLOAT
SET @a = 684864.65413734536
SELECT @a
SELECT CAST(@a AS NVARCHAR(4000))
SELECT @a

When I run this I get :
684864.654137345
684865
684864.654137345

Equally, if I run:

declare @a FLOAT
SET @a =0.123456789
SELECT @a
SELECT CAST(@a AS NVARCHAR(4000))
SELECT @a

I get :
0.123456789
0.123457
0.123456789

Constructive suggestions appreciated.

nmarks
Yak Posting Veteran

53 Posts

Posted - 2008-01-06 : 05:57:44

I have now found the solution to this problem : by redefining the FLOAT as, say, as DECIMAL(20,20) the CAST function preserves the accuracy of the number.

If I run declare @a DECIMAL(20,20)
SET @a =0.123456789123456789
SELECT @a
SELECT CAST(@a AS NVARCHAR(4000))
SELECT @a

I get :
0.12345678912345678900
0.12345678912345678900
0.12345678912345678900

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources