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

sql-server currency
senthil picture senthil · Feb 10, 2009 · Viewed 104.8k times · Source

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

Answer

Jonathan picture Jonathan · Feb 10, 2009

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))

Related questions

When to use Money data or Decimal Data type in sql server to store costing values?

What is preferable data type to store the costing value ? Money or Decimal whats the programmatic diff bet them? asp.net Data Annotation for asp.net Model for Money data type ?

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More
How do I perform an IF...THEN in an SQL SELECT?

How do I perform an IF...THEN in an SQL SELECT statement? For example: SELECT IF(Obsolete = 'N' OR InStock = 'Y' ? 1 : 0) AS Saleable, * FROM Product

Read More