SQL datatype for money??

toString · 9 Dec 2006 at 17:02

I started off using varchar because I could input:

29,000

But then I wanted to use this field for calculations too:

Code:

SELECT Salary, ROUND((Salary / 12),2) AS MonthlySalary

This caused an error, obviously. So I changed to decimal type (although the same seems to happen for double and int)
So now my output is like this:

29000
2416.67

Unfortunately I've lost the lovely , which separates the thousands from the hundreds....
Is there anyway to get the best of both worlds? :confused:

rpstewart · 9 Dec 2006 at 17:16

What are you using to get/display the output, raw SQL?

Casting the decimal back to a char or varchar won't put the comma back in so if you want to do it in SQL you'd need to cast to a varchar and then start using combinations of length(), concat() and substr() functions to break the string up and insert the comma at the appropriate point.

Or you could just chuck the data into excel and set the cell formatting appropriately.

robmiller · 9 Dec 2006 at 17:21

Use an INT to store the number of pennies, then convert to pounds for display. If you use store things as a FLOAT (such as 124.45 for £124.45) you'll get rounding errors and end up losing money.

HTMLHugo · 9 Dec 2006 at 19:42

ive always been told by our DBA's to use DECIMAL(10,2) for money

not being a jedi at sql i just do what im told is best

although robs method would do the trick too

Thrash · 9 Dec 2006 at 22:36

Why not use the money or smallmoney datatypes?

rpstewart · 9 Dec 2006 at 22:45

Thrash said:
Why not use the money or smallmoney datatypes?

Those types only exist in SQL Server, there's no indication that the OP is using that.

toString · 9 Dec 2006 at 22:52

Yeah sorry I'm using mySQL. Don't you hate it when people don't tell you what they're using.....

I guess I should just create some conversion function. Although I'm not entirely fond on having to convert to pennies, maybe that's the correct way, but this isn't a 'live' app

Thanks guys!