Currency Fromatting

Hi Everyone,
I am a bit of a newbie to SQL, I have created a table to hold supplier
invoice details which has three columns to hold currency values i.e.
Nett Value
VAT Value
Gross Value
The columns for VAT and gross calculate automatically from the Nett value,
however the values in the fields have more than 2 digits after the decimal
place i.e.
Nett Value = 275.00
VAT Value = 48.125
Gross Value = 323.125
How do I force the two calculating fields to format the contents to standard
currency format i.e. ￡xxx.xx with only 2 digits after the decimal point?
Hope that makes sense. Any help would be greatly appreciatedNote that in the UK (AFAIK same as the rest of the EU), accounting
regulations require that VAT be calculated on an invoice sub-total, not
on individual items (there may be a difference due to rounding) so what
you are doing will not represent the correct total for accounting
purposes.
Storing both Net and Gross columns in a table is poor design. This is
redundant data and most systems will store just the Net value at detail
level, plus a VAT code.
Formatting is probably best left to your client app or middle tier. If
you must do it in SQL then look up the style parameter of the CONVERT
function. Avoid using MONEY / SMALLMONEY columns in your tables though
as these have some problems with precision in division operations.
David Portas
SQL Server MVP
--|||Thanks for your reply David. I am aware that for official accounting it
would need to be line by line calculated, but my DB if purely for reporting
supplier turnover and not for accounting so I am not overly bothered about
the roundings, but thanks for the pointers I'll go and have aplay and see
what I get.
Cheers
Jonathan
"David Portas" wrote:

> Note that in the UK (AFAIK same as the rest of the EU), accounting
> regulations require that VAT be calculated on an invoice sub-total, not
> on individual items (there may be a difference due to rounding) so what
> you are doing will not represent the correct total for accounting
> purposes.
> Storing both Net and Gross columns in a table is poor design. This is
> redundant data and most systems will store just the Net value at detail
> level, plus a VAT code.
> Formatting is probably best left to your client app or middle tier. If
> you must do it in SQL then look up the style parameter of the CONVERT
> function. Avoid using MONEY / SMALLMONEY columns in your tables though
> as these have some problems with precision in division operations.
> --
> David Portas
> SQL Server MVP
> --
>