June 5, 2009 at 3:55 am
Hello everybody,
Good Afternoon.
I am trying to store my data into a temporary table for a report.
In that I am storing the heading and values. While inserting the float values into the column it is storing in different format. Please give me some suggestion....
create table #d (column3 varchar(255))
declare @var float
set @var = 654646548463.41646646
insert into #d
select 'column-Headding' union
select cast(@var as varchar)
select * from #d
--------------------------------
storing value as 6.54647e+011
--------------------------------
With advanced Thanks,
🙂
June 5, 2009 at 5:42 am
This gets you closer, though some decimals are still lost.
if object_id('tempdb..#d') is not null drop table #d
create table #d (column3 varchar(255))
declare @var float
set @var = 654646548463.41646646
insert into #d
select 'column-Headding' union all
select cast(@var as varchar) union all
select convert(varchar(255), @var, 2) UNION ALL
select str(@var, 21, 8)
select * from #d
results:
column-Headding
6.54647e+011
6.546465484634165e+011
654646548463.41650000
I'm not sure why it's being rounded up at the 4th decimal spot (yet).
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 5, 2009 at 6:49 am
I'm not sure why it's being rounded up at the 4th decimal spot (yet)
Because if the number is too big, decimals are sacrificed to make it fit
A better answer is that there are too many signicant digits and the mantissa is reduced to make the number fit
Far away is close at hand in the images of elsewhere.
Anon.
June 5, 2009 at 10:05 pm
David Burrows (6/5/2009)
I'm not sure why it's being rounded up at the 4th decimal spot (yet)
Because if the number is too big, decimals are sacrificed to make it fit
A better answer is that there are too many signicant digits and the mantissa is reduced to make the number fit
Okay...
In BOL (for the REAL data type), it states:
float [ ( n ) ]
Where n is the number of bits that are used to store the mantissa of the float number in scientific notation and, therefore, dictates the precision and storage size. If n is specified, it must be a value between 1 and 53. The default value of n is 53.
So, float = float(53).
I find that for float(24), I get 654646575104.00000000, while for float 25-53 I get 654646548463.41650000. Now it seems to me that if float(25) does this rounding off, that somewhere between 25 and 53 there ought to be enough space to store what is needed for these next 4 decimal places.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 6, 2009 at 5:36 am
The entry in BOL has always been badly worded and confusing, at least to me.
It goes on to say: "SQL Server treats n as one of two possible values. If 1<=n<=24, n is treated as 24. If 25<=n<=53, n is treated as 53."
So there really is no difference between float(25) and float(53). They're both float(53) aka double precision, which is limited to 15 significant digits. Hence the rounding.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 6, 2009 at 7:10 am
Paul White (6/6/2009)
The entry in BOL has always been badly worded and confusing, at least to me.It goes on to say: "SQL Server treats n as one of two possible values. If 1<=n<=24, n is treated as 24. If 25<=n<=53, n is treated as 53."
So there really is no difference between float(25) and float(53). They're both float(53) aka double precision, which is limited to 15 significant digits. Hence the rounding.
Thanks for pointing that out. That explains a lot.
So, since real is a synonym for float(24), all you really need to play with is real and float (w/o a specific declaration).
Thanks Paul. Once again, I've started my day by learning something about SQL that I didn't know before.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
June 6, 2009 at 7:56 am
Cool!
BTW that's pretty much where my expertise with reals and floats ends - I think I have a fair grasp of how they are implemented, and some of their strengths and weaknesses, but I still find the whole float-vs-fixed debate very tough going. I tend not to use floats, not because they are somehow 'inaccurate' or whatever, more because there are subtleties which I do not fully grok.
For example, apparently floats are the fastest way to manipulate integers up to 253 - or something. I don't think I'd be brave enough to do it though for lack of depth of knowledge reasons.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
June 6, 2009 at 2:37 pm
So there really is no difference between float(25) and float(53). They're both float(53) aka double precision, which is limited to 15 significant digits. Hence the rounding.
Nice one Paul 🙂
Wish I had put it so eloquently :blush:
I think I knew what I wanted to say but just could not do it so well :crying:
Far away is close at hand in the images of elsewhere.
Anon.
June 7, 2009 at 1:28 am
David Burrows (6/6/2009)
I think I knew what I wanted to say but just could not do it so well :crying:
Hey David, I'm sure your answer was better for anyone who knows what the exponent, radix and significand are.
I aim slightly lower! 😀
Between us I reckon we covered most of the potential audience...
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply