• Gopi S (10/13/2012)


    Hi,

    In the expression [price * 1.1], SQL Server tries to implicit convert price into numeric(2,1) because 1.1 is type numeric(2,1) which has higher precedence than nvarchar. I think your table should have values greater that 10.

    say 10.1, 10.2...... where the precision value is exceeding 2.

    To resolve this try

    update table set price = price * cast(1.1 as numeric(10,4))

    Note: If your price still has more that 10 precision value then change it accordingly.

    Thanks

    Gopi

    Yes, it's the implicit conversion that's going wrong because the default type for 1.2 is numeric(2,1). But there's another problem: if you try to increase a price like 10.37 by 10%, just multiplying by 1.1 doesn't work because it will deliver 11.387 which may not be a valid currency amount (eg if the currency is euro the smallest value is .01, so .007 is invalid) and you have to decide what rounding you want to do in this case - perhaps rounding to nearest valid number (and what ever you want to do to round something ending in .xy5) or perhaps rounding down or perhaps some other rule.

    So I think I would go for something like

    update table

    set price = convert(nvarchar(36), cast(cast(price as numeric(35,2))*1.1 as numeric(35,2)))

    (the inner cast should solve the error; the outer cast does simple default rounding, so replace it with something that does whatever sort or rounding you need if the default isn't what you want)

    Of course if you have a currency unit that splits into 1000 parts instead of 100, you need to use scale 3 instead of scale 2, and so on for other splits. If you will sometimes want to multiply by something more complicated than 1.1 (for example by 1.0625, for a 6¼% increase) you need to reduce the precision from 35 to avoid the possibility of the multiply operation doing some rounding instead of whatever rounding you have chosen to do - pushing it down to 32 isn't going to put much of a constraint on your prices, unless you have prices of 1000000000000000000000000000000.00 currency units or more. In fact you could just look and see what your nvarchar price column is declared as: if it's nvarchar(N) you should use precision N-1, unless whoever specified it was being sloppy.

    Tom