PJ on Development: Blog

As I told in another article, the company I work for is installing an ERP software and I was given the task of migrating the invoice data from the old database to the new one.

Although both databases are SQL Server 2000 I got stuck with a rounding problem because the datatypes used by the databases are different.

On our old database we use the money datatype while the new database uses the float datatype.

I don't know how the ERP software deals with the values internally because they use yet another layer between the software and the database server, but the rounding problem remains.

I did some testing and came up with the table below.

As you can see there are some roundings that the float datatype does not handle very well.

So, I think that float should not be used when precision and robustness are required.

Edited to Add: On SQL Server 2005 the rounding problems with float apparently does not exist, as I did the same test that produced this table in SQL Server 2000 and I could not find any weirdness.

n	money	float
0	.0000	0.0
1	.0100	0.01
2	.0200	0.02
3	.0300	2.9999999999999999E-2
4	.0400	4.0000000000000001E-2
5	.0500	5.0000000000000003E-2
6	.0600	5.9999999999999998E-2
7	.0700	7.0000000000000007E-2
8	.0800	8.0000000000000002E-2
9	.0900	8.9999999999999997E-2
10	.1000	0.10000000000000001
11	.1100	0.11
12	.1200	0.12
13	.1300	0.13
14	.1400	0.14000000000000001
15	.1500	0.14999999999999999
16	.1600	0.16
17	.1700	0.17000000000000001
18	.1800	0.17999999999999999
19	.1900	0.19
20	.2000	0.20000000000000001
21	.2100	0.20999999999999999
22	.2200	0.22
23	.2300	0.23000000000000001
24	.2400	0.23999999999999999
25	.2500	0.25
26	.2600	0.26000000000000001
27	.2700	0.27000000000000002
28	.2800	0.28000000000000003
29	.2900	0.28999999999999998
30	.3000	0.29999999999999999
31	.3100	0.31
32	.3200	0.32000000000000001
33	.3300	0.33000000000000002
34	.3400	0.34000000000000002
35	.3500	0.34999999999999998
36	.3600	0.35999999999999999
37	.3700	0.37
38	.3800	0.38
39	.3900	0.39000000000000001
40	.4000	0.40000000000000002
41	.4100	0.40999999999999998
42	.4200	0.41999999999999998
43	.4300	0.42999999999999999
44	.4400	0.44
45	.4500	0.45000000000000001
46	.4600	0.46000000000000002
47	.4700	0.46999999999999997
48	.4800	0.47999999999999998
49	.4900	0.48999999999999999
50	.5000	0.5
51	.5100	0.51000000000000001
52	.5200	0.52000000000000002
53	.5300	0.53000000000000003
54	.5400	0.54000000000000004
55	.5500	0.55000000000000004
56	.5600	0.56000000000000005
57	.5700	0.56999999999999995
58	.5800	0.57999999999999996
59	.5900	0.58999999999999997
60	.6000	0.59999999999999998
61	.6100	0.60999999999999999
62	.6200	0.62
63	.6300	0.63
64	.6400	0.64000000000000001
65	.6500	0.65000000000000002
66	.6600	0.66000000000000003
67	.6700	0.67000000000000004
68	.6800	0.68000000000000005
69	.6900	0.68999999999999995
70	.7000	0.69999999999999996
71	.7100	0.70999999999999996
72	.7200	0.71999999999999997
73	.7300	0.72999999999999998
74	.7400	0.73999999999999999
75	.7500	0.75
76	.7600	0.76000000000000001
77	.7700	0.77000000000000002
78	.7800	0.78000000000000003
79	.7900	0.79000000000000004
80	.8000	0.80000000000000004
81	.8100	0.81000000000000005
82	.8200	0.81999999999999995
83	.8300	0.82999999999999996
84	.8400	0.83999999999999997
85	.8500	0.84999999999999998
86	.8600	0.85999999999999999
87	.8700	0.87
88	.8800	0.88
89	.8900	0.89000000000000001
90	.9000	0.90000000000000002
91	.9100	0.91000000000000003
92	.9200	0.92000000000000004
93	.9300	0.93000000000000005
94	.9400	0.93999999999999995
95	.9500	0.94999999999999996
96	.9600	0.95999999999999996
97	.9700	0.96999999999999997
98	.9800	0.97999999999999998
99	.9900	0.98999999999999999