SQL SERVER Query Analyser 2000 - FLOAT column shows 33.87 value as 33.86999999
-
thanks a lot everyone... can we say money(10,2)... I really want only 2 decimal places.... will this work???? i know i should read manual as someone pointed out but trust me iam in big hole and i dont have time to experiment something on my own or read or research... I need expert opinion from experts here...
so dont mind... plz advise...
thanks a lot once again...
-
Please read at least a short section about datatypes in BOL. You will find out that MONEY datatype has no parameters that would allow to modify the size (number of digits).MONEY or SMALLMONEY datatype have always 4 decimal places and in my opinion, one or two more decimal places than absolutely necessary is a good thing. Go with MONEY. If you really need to display less than 4 decimal places, do so on the presentation level.
-
Here is a sample of the two options
CREATE TABLE [dbo].[T_Money_Test] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[moneyField] [money] NULL ,
[DECField] [DECIMAL] (10,2) NULL
) ON [PRIMARY]
GO
INSERT INTO [ROSK_SWL].[dbo].[T_Money_Test]
([moneyField]
,[DECField])
VALUES
(123.23,
45.67)
INSERT INTO [ROSK_SWL].[dbo].[T_Money_Test]
([moneyField]
,[DECField])
VALUES
(1.2345,
45.6757)
SELECT [ID]
,[moneyField]
,[DECField]
FROM [ROSK_SWL].[dbo].[T_Money_Test]
Results in
1 123.23 45.67
2 1.2345 45.68
From BOL
Monetary Data
Monetary data represents positive or negative amounts of money. In Microsoft® SQL Server™ 2000, monetary data is stored using the money and smallmoney data types. Monetary data can be stored to an accuracy of four decimal places. Use the money data type to store values in the range from -922,337,203,685,477.5808 through +922,337,203,685,477.5807 (requires 8 bytes to store a value). Use the smallmoney data type to store values in the range from -214,748.3648 through 214,748.3647 (requires 4 bytes to store a value). If a greater number of decimal places are required, use the decimal data type instead.
Good Holiday!
Steve
-
I agree with Vladan... spend some time with Books Online...
Although I think MONEY is the correct datatype to use here, using DECIMAL(10,2) won't kill you if you don't mind automatic rounding to the penny... but as I and others have said, why is it a problem to store 4 decimal places and display as 2? And, if the "presentation level" is actually SQL Server (for exports, etc), then you might (should) want to curl up with Books Online on the following functions... (notice that STR and conversion to CHAR provides right-hand justification, as well)...
DECLARE @Money MONEY
SET @Money = 123456.7891
SELECT STR(@Money,10,2) AS [Str],
CONVERT(VARCHAR(13),@Money,0) AS MVc0,
CONVERT(VARCHAR(13),@Money,1) AS MVc1,
CONVERT(VARCHAR(13),@Money,2) AS MVc2,
CONVERT(CHAR(13),@Money,0) AS MC0,
CONVERT(CHAR(13),@Money,1) AS MC1,
CONVERT(CHAR(13),@Money,2) AS MC2,
'$'+CONVERT(VARCHAR(13),@Money,1) AS DSVC,
'$'+CONVERT(CHAR(13),@Money,1) AS DSC
... from there, you can let your imagination go wild... yeah, you can do some of the same formatting with DECIMAL(10,2) and if you need the commas you can always convert it to MONEY and then to CHAR/VARCHAR.
Just in case there's anyone out there that still thinks float has any type of accuracy for this type of thing, try this simple test...
DECLARE @Dividend FLOAT
DECLARE @Divisor FLOAT
SET @Dividend = 1
SET @Divisor = 10
SELECT @Dividend/@Divisor
... or a slightly more impressive display of inaccuracy (please pardon the loop
) ...DECLARE @FloatTest TABLE (FloatValue FLOAT)
DECLARE @Divisor FLOAT
SET @Divisor = 10
DECLARE @Counter INT
SET @Counter = 1
WHILE @Counter <= 100
BEGIN
INSERT INTO @FloatTest VALUES(@Counter/@Divisor)
SET @Counter = @Counter + 1
END
SELECT * FROM @FloatTest
I'm not sure I'd use FLOAT even for scientific calculations... the small bit of inaccuracy of the FLOAT datatype could cause you to land your Martian Lander a foot or two on the wrong side of the surface of Mars
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks everyone. The reason I want decimal with 2 decimal places is because its a standard for all of our clients & we use it to import the data in our tables and export the data as a text file to the customers.
Presenatation layer is Coldfusion and I hate to change presentation layer or any database queries to just display money with 2 decimal places when I have the option to do the same with Decimal(10,2).
Makes sense???
Thanks a lot once again.
-
This is not to confuse the original post-er.
I ran the example above on Query Analyzer connected to SQL Server 2000 and then on the same machine Enterprise Studio connected to the same SQL Server 2000. Here are the results. It is interesting that Enterprise Studio corrects the Float problem or appears to. Thoughts or comments?
0.10000000000000001
0.20000000000000001
0.29999999999999999
0.40000000000000002
0.5
0.59999999999999998
0.69999999999999996
0.80000000000000004
0.90000000000000002
1.0
1.1000000000000001
1.2
1.3
1.3999999999999999
1.5
1.6000000000000001
1.7
1.8
1.8999999999999999
2.0
2.1000000000000001
2.2000000000000002
2.2999999999999998
2.3999999999999999
2.5
2.6000000000000001
2.7000000000000002
2.7999999999999998
2.8999999999999999
3.0
3.1000000000000001
3.2000000000000002
3.2999999999999998
3.3999999999999999
3.5
3.6000000000000001
3.7000000000000002
3.7999999999999998
3.8999999999999999
4.0
4.0999999999999996
4.2000000000000002
4.2999999999999998
4.4000000000000004
4.5
4.5999999999999996
4.7000000000000002
4.7999999999999998
4.9000000000000004
5.0
5.0999999999999996
5.2000000000000002
5.2999999999999998
5.4000000000000004
5.5
5.5999999999999996
5.7000000000000002
5.7999999999999998
5.9000000000000004
6.0
6.0999999999999996
6.2000000000000002
6.2999999999999998
6.4000000000000004
6.5
6.5999999999999996
6.7000000000000002
6.7999999999999998
6.9000000000000004
7.0
7.0999999999999996
7.2000000000000002
7.2999999999999998
7.4000000000000004
7.5
7.5999999999999996
7.7000000000000002
7.7999999999999998
7.9000000000000004
8.0
8.0999999999999996
8.1999999999999993
8.3000000000000007
8.4000000000000004
8.5
8.5999999999999996
8.6999999999999993
8.8000000000000007
8.9000000000000004
9.0
9.0999999999999996
9.1999999999999993
9.3000000000000007
9.4000000000000004
9.5
9.5999999999999996
9.6999999999999993
9.8000000000000007
9.9000000000000004
10.0
But using Enterprise Studio 2005 connected to a SQL Server 2000 I got
0.1
0.2
0.3
0.4
0.5
0.6
0.7
0.8
0.9
1
1.1
1.2
1.3
1.4
1.5
1.6
1.7
1.8
1.9
2
2.1
2.2
2.3
2.4
2.5
2.6
2.7
2.8
2.9
3
3.1
3.2
3.3
3.4
3.5
3.6
3.7
3.8
3.9
4
4.1
4.2
4.3
4.4
4.5
4.6
4.7
4.8
4.9
5
5.1
5.2
5.3
5.4
5.5
5.6
5.7
5.8
5.9
6
6.1
6.2
6.3
6.4
6.5
6.6
6.7
6.8
6.9
7
7.1
7.2
7.3
7.4
7.5
7.6
7.7
7.8
7.9
8
8.1
8.2
8.3
8.4
8.5
8.6
8.7
8.8
8.9
9
9.1
9.2
9.3
9.4
9.5
9.6
9.7
9.8
9.9
10
-
The "app" cleaned it up... the number is still stored in the db with the inaccuracy.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply