currency - How do I convert from a money datatype in SQL server?

Question

I have a money data type in SQL Server. How do I reformat 0.00 to 0 in my query?

Answer 1

Normal money conversions will preserve individual pennies:

SELECT convert(varchar(30), moneyfield, 1)

The last parameter decides what the output format looks like:

0 (default) No commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 4235.98.

1 Commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 3,510.92.

2 No commas every three digits to the left of the decimal point, and four digits to the right of the decimal point; for example, 4235.9819.

If you want to truncate the pennies, and count in pounds, you can use rounding to the nearest pound, floor to the lowest whole pound, or ceiling to round up the pounds:

SELECT convert(int, round(moneyfield, 0))
SELECT convert(int, floor(moneyfield))
SELECT convert(int, ceiling(moneyfield))