|
This SQL Statement:
SELECT CAST(2.78 AS FLOAT)
Returns in SQL Server 2005 as: 2.78
But....
Returns in SQL Server 2000 as: 2.7799999999999998
Obviously 2.78 <> 2.7799999999999998
Where can I set my SQL Server 2000 to not do this!
_______________________________________________________________________
http://www.readytogiveup.com/[ ^]
"you can't forget something you never knew..." M. Du Toit
|
|
|
|
|
decimal can be used instead of Float
CAST(2.78 AS Decimal(5,2))
Regards
KP
|
|
|
|
|
Ah, time to post What Every Computer Scientist Should Know About Floating Point[^] again.
The value 2.78 cannot be stored exactly in a 32-bit floating point field. The closest approximation has the bit pattern 40 31 EB 85 (in hex), which is 1.011 0001 1110 1011 1000 0101 left-shifted by 1, i.e. 10.11 0001 1110 1011 1000 0101. (See IEEE 754[^] on Wikipedia.)
Converting to decimal you get:
1 * 2 ^ 1 = 2 + 2
0 * 2 ^ 0
1 * 2 ^ -1 = 0.5 + 2.5
1 * 2 ^ -2 = 0.25 + 2.75
0 * 2 ^ -3
0 * 2 ^ -4
0 * 2 ^ -5
1 * 2 ^ -6 = 0.015625 2.765625
1 * 2 ^ -7 = 0.0078125 2.7734375
1 * 2 ^ -8 = 0.00390625 2.77734375
1 * 2 ^ -9 = 0.001953125 2.779296875 You can see that the approximation is getting closer to 2.78, but the exact value is not possible as 0.03 (the residual after the first four bits) is not representable as a sum of negative powers of two. In the same way 1/3 is not representable exactly as a sum of negative powers of 10, or a decimal as we normally call it - the closest we can get is 0.33333 (and the 3s continue infinitely).
You can also see by the fact that the approximation is getting longer in decimal digits that a complete representation of the binary value will take a lot of space. I believe that a binary floating point value is always fully representable as a decimal (two dividing evenly into ten) but the space requirement may be unacceptable. Therefore, the value is almost always shown rounded to a certain number of places.
SQL Server does not have particularly good display-control features. That's not what it's for - it's for retrieving data efficiently. Instead you should use the typed-dataset mechanisms of whatever your client programming language or environment is - for example, the SqlDataReader in .NET - to retrieve the data without interpretation, then format the result using the programming language/environment's features (e.g. String.Format).
If you want to preserve the decimals accurately, you should use one of the scaled integer data types (e.g. decimal which is a synonym for numeric). These simply record the value without the decimal point, and a decimal exponent (e.g. here it would record 278 with an exponent of -2: 2.78 = 278 * 10 ^ -2).
|
|
|
|
|
5!
_______________________________________________________________________
http://www.readytogiveup.com/[ ^]
"you can't forget something you never knew..." M. Du Toit
|
|
|
|
|
5 for the best explanation ever.
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."
My website
|
|
|
|
|
Worth a 5 for the most comprehensive explanation ever put forward in the forums.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Hi.
What is a query that returns (for example) 5 records that a specified field is greater than otheres?
I have a table for news and this table has a numOfVisit field, now I want to return 5 most visited news.
(5 records that numOfVisit field is most greates)
Best wishes
|
|
|
|
|
select top 5 * from tablename order by numofvisit desc
gauthee
|
|
|
|
|
Use the TOP(xxx) command to limit returned values.
And the ORDER BY command to order these into the most visited.
For example
SELECT TOP(5) NewsID, NewsTitle, NewsText<br />
FROM NEWSTABLE<br />
ORDER BY numOfVisit ASC
You may need to use DESC instead of ASC depeding on which way round you want the results.
Any good?
|
|
|
|
|
Hi,
How do ranks work in full text searches? I don't understand? Must I go and setup ranks any where?
Regards,
ma se
|
|
|
|
|
CREATE PROCEDURE salary2
(
@projectid as int,
@months as tinyint,
@years as smallint,
@Insunday as smallint,
@Outsunday as smallint
)
As
declare
@countinstall as smallint
begin
select a.empid,b.empName,c.Whrs,c.Bassalary,c.hra,c.ca,c.ota,C.ot,c.Esi,c.srg,
case when (isnull(srg,0))=0 then @Outsunday else @Insunday end as days,c.Edate
from tblMonthHourDetails a,tblemployeemaster b,tblsalarydetails c where a.empid=b.empid and a.empid=c.empid and
b.projectid=@projectid and datepart(mm,[SDate])=@months and datepart(yy,[SDate])=@Years
group by a.empid,b.empName,c.Whrs,c.srg,c.Edate
end
GO
Hi in this stored procedure working...,
But The same tblsalarydeatils empid have more than one record...,
So i like to take the final entry record from that tblsalarydeatils empid based on the edate field...,
So, i have to use Order by desc method...,
The query should like this format(For example taken a paticular empid):
Select top 1 * from tblmonthsalarydetails where empid=608 order by edate desc
But see in my procedure i already used group by...,
So how to say in that, for the empid take latest record like?,
Means how to use there " order by edate desc"...,
Plz any one hlp me...,
Or give some solution for this probs,
Advance Thanks,
-- modified at 2:35 Monday 30th July, 2007
-- modified at 2:38 Monday 30th July, 2007
-- modified at 2:39 Monday 30th July, 2007
Regards,
Magi
|
|
|
|
|
After your group by clause just add order by clause.
i.e. select........ group by...... order by ... desc
gauthee
|
|
|
|
|
see if i use order by edate desc then which one have the highest date that record only displaying Others are not displaying ,
Regards,
Magi
|
|
|
|
|
Magi,
When you use group by the result would always be groupwise so that might be the reason it is howing date only for that group.
An alernative - create a view and write your query using group by then on the view you can use the top keyword and retrieve your records!
select top n * from view_name orderby column_name
gauthee
|
|
|
|
|
Hi what to value want to give in the n place...,
Plz understand my situation...,
See in that stored procedure...,
And give ur ideas...,
In that stored procedure i cant say n like...,
In that place i want to show some value...,
But that also after dispaly the records only i will get...,
Regards,
Magi
|
|
|
|
|
You said there would be more than one record with the same empno and you wnat the latest one, is there a field to identify the latest record of the two records?
gauthee
|
|
|
|
|
Ya for same employee have more than one record, Same time i like to show other employee details also like that...,
If i use order by edate procedure then i am getting which employee have recent date record that one only displaying...,
Tahts the probs...,
Regards,
Magi
|
|
|
|
|
You have been told before not to use 'urgent' or 'help me' in your message titles.
_____________________________________________
Flea Market! It's just like...it's just like...A MINI-MALL!
|
|
|
|
|
Hey People
Had a funny question to ask.
Want to get a new server with 2 x quad core cpu.
Now sql server 2000 standard supports 4 cpu but will sql accept 2*quad=8 into cosideration or the actual hardware cpus which is 2 ???
|
|
|
|
|
Not sure. Have you looked around at Microsoft's site? I would imagine there are benefits to running SQL Server 2000 on a quad core.
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
AFAIK for all microsoft software when they refer to a CPU limit they mean each physical CPU, no matter how many cores each CPU has. This definatly applies to thier operating systems and I belive it applies to all of thier products.
Your best bet would be to give them a quick call and ask, that way you'll definatly know 
|
|
|
|
|
From the SQL 2k5 licencing document download on Microsoft's SQL Server Licensing Page[^]
"Multicore processors, which consist of multiple processing execution units or “cores” on one chip, are seen as a promising way to boost computing power. Microsoft has been driving thought leadership in this area by charging the same amount per processor, regardless of how many cores are in the processor."
|
|
|
|
|
hi everyone
please give me hint how i can do following:
I have inserted data into database using textboxes and database gets updated. Each record contains 5 fields. When I submit data from textboxes I want next page appear and show data.
But wait ......... i need each record as below
Name: abc
address: def street
phone: 1234
email: a11111@gmail.com
above is one record.
I want to if i feed new data in form text boxes. This new page show block of data of each record (new at the top) as given above.
Please guide me how can i do that ... if any article in this regard on code project please mention.
Thank You.
destined to win
|
|
|
|
|
u can use addition field in to ue database "register_date_time"
and after every affected u have to reload ur data from ur db .
be carefull u must to use orderby command with desc parameter in ur sql command.
good luck
msma
|
|
|
|
|
Hi All,
Is it possible to convert SQL Server 2000 database file to Sql Server 2005 .mdf file. If it is possible then please tell me how to do it.
Thanx in Advance.
Regards,
Paramhans Dubey.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
