Although not conclusive, based on the following tests it looks like SharePoint handles numbers in a way suitable for monetary calculations (at least with WSS 3.0/SQL Server 2005) even though they are stored approximately. However, values with over 15 significant figures can exhibit rounding errors:
Test for money storage and retrieval
Three number columns (First, Second and Third) containing 3.7, 3.65 and 0.05 respectively (.Net example from here) and a calculated column (returning a single line of text) with the following formula: =IF(First=Second+Third,"Success","Failure"). On viewing the list the calculated column displays Success.
Test for money calculations
A Yes/No calculated column with the formula =0.1+0.1+0.1=0.3 (.Net example from here). On viewing the list the calculated column displays Yes.
Test for money storage and calculation I
In a list called TestList, a custom number column (CustomNumber) contains 304253.3251 (SQL Server example from Microsoft White Paper). This is stored in database table AllUserData, column float1 of type float (SQL Server 2005). float is an approximate data type.
Running the following queries:
DECLARE @ListName UNIQUEIDENTIFIER SET @ListName = (SELECT tp_Id FROM AllLists WHERE tp_Title = 'TestList') SELECT CAST(float1 as NUMERIC(18, 11)) AS CustomNumber FROM AllUserData WHERE tp_ListId = @ListName SELECT float1 AS CustomNumber FROM AllUserData WHERE tp_ListId = @ListName
Gives the following results:
CustomNumber 304253.32510000002 CustomNumber 304253.3251
Creating a calculated column with the formula =CustomNumber*100000000000 which might be expected to display the incorrect value of 30425332510000002 actually displays the correct (from the user's perspective) value of 30,425,332,510,000,000.00000. I assume that this behaviour is due to the code reading the float value from the database doing a cast to numeric with a suitably small number of decimal places and manipulating the values in memory using the .Net Decimal type.
There are suggestions, however, that on SQL Server 2000 calculation errors may manifest in this case since the behaviour of float values has been altered between the versions.
Test for money storage and retrieval II
Based on the results from the previous test add the following value to the CustomNumber column: 9999999999999999.
This 16 significant digit value is displayed incorrectly in the list (and edit) views as 10,000,000,000,000,000 (using a value with 15 digits displays correctly).
Inspecting the AllUserData table shows that the value is stored incorrectly in the database and running the following query:
DECLARE @f FLOAT SET @f = 9999999999999999 SELECT CAST(@f as NUMERIC(20, 0))
Gives the result:
10000000000000000
Which demonstrates that SQL Server is rounding the number on insertion.
