Search code examples
sqlsql-server-2008sqldatatypes

SQL Data type for monetary values with up to 15 decimal places

I have a need to store and do some basic calculations (mostly multiplication) on monetary values like £0.00005756, £0.01, £0.000000000000123, etc. The maximum number of decimal places is going to be 15 but there could be fairly large numbers on the other side too. If there's more than a 0 on the left side it's unlikely that more than 2 decimal places would be utilised but it is possible. The numbers I have had trouble with are when it goes above 2 decimal places.

I created a test table and inserted some basic data. Unfortunately it can't seem to store really small numbers.

Note: I created the money2 column as [decimal](18, 4) column as this seems to be recommended when I've researched what to use for monetary values. However, I need 15 decimal places, which is why money1 exists.

Table creation:

  CREATE TABLE [dbo].[MoneyTest](
        [id] [int] IDENTITY(1,1) NOT NULL,
        [money1] [decimal](18, 15) NULL,
        [money2] [decimal](18, 4) NULL,
     CONSTRAINT [PK_uMoneyTest] PRIMARY KEY CLUSTERED 
    (
        [id] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    GO

insert into moneytest 
(money1, money2) values
(£0.01,£0.01)

insert into moneytest 
(money1, money2) values
(£0.000000000000123,£0.000000000000123)

insert into moneytest 
(money1, money2) values
(£0.00005756,£0.00005756)

select * from moneytest returns the following results:

id  money1              money2
7   0.010000000000000   0.0100
8   0.000000000000000   0.0000
9   0.000100000000000   0.0001

I was expecting these results though:

id  money1              money2
7   0.010000000000000   0.0100
8   0.000000000000123   0.0000
9   0.000575600000000   0.0001

In particular, row id 9 seems to be rounding up to a 1 (at decimal point 4) instead of showing the actual figure. I thought this only happened when using floating point columns that estimate numbers and that decimal was meant to be more accurate?

Is this just a datatype problem or is it something that I shouldn't be trying to resolve in the database at all?

Solution

  • The problem is the pound-sign. It means that the values are being read as money -- which is not high enough precision. So, you are losing decimal places.

    Just remove the currency symbol:

    insert into moneytest (money1, money2)
    values (0.000000000000123, 0.000000000000123);

    Here is a db<>fiddle.