0

I have a column that is currency displayed as a numeric field. So, '$3.15' is recorded as '315'. When doing my query, I'm looking to place a decimal two places into these numbers so that 2066 becomes 20.66 and 315 becomes 3.15

I know it's most likely an easy cast or convert, but I have searched all around and I cannot find a solution. I'm building my query in SQL Server 2005 Express.

Thanks in advance for the help!

Anthony Morack
  • 29
  • 2
  • 6
  • You really don't want to do this. Currency formatting should be done in the application layer. It is also a little bit more complex than you describe here. You're dealing with locales (in Europe most people use the comma instead of the period), currency precision, currency symbol, etc. None of that should be done by the database. – wvdz Jun 19 '14 at 13:57
  • Thank you popovitsj, I agree. Unfortunately I'm writing a query for a client against a database that is preexisting and I do not have any access to. – Anthony Morack Jun 19 '14 at 14:32

1 Answers1

0

The problem is that when you divide two integer values, the result will be an integer too, and it will be truncated. (Called: integer math)

SELECT 2066/100 -- Output 20

To avoid truncation, you should cast one of the arguments to a datatype, which can represent the result value:

SELECT 2066 / CONVERT(DECIMAL(19,2), 100) -- Output 20.660000

To force the precision and scale, you shoud convert the result too:

SELECT CONVERT(DECIMAL(19,2), (2066 / CONVERT(DECIMAL(19,2), 100))) -- Output 20.66

As a general advice:

  • To store these values, use DECIMAL or NUMERIC data types insted of integers. Choose a precision and scale which will fit to your needs (including summarizing the data, calculating averages, and so on)
  • All formatting should be done when you display the data (in the application) insted of in the database server.
Pred
  • 8,789
  • 3
  • 26
  • 46