SQL Server money data type problem

Question

We have SQL Server 2000 database with money data type columns and we have strange problem with numbers within money columns in which we store numbers with 2 decimal places. For o long time everything was OK. But now I see that in some rows where was number 47.22 is now number 47.2178. When i select CAST(COLUMN as CHAR) result is 47.22 but when i retrieve value from ADO recordset i have result 47.2178. I browse all application if there is any place where it can write number with 4 decimal places and find nothing(and in application history log there are records that application writes 47.22 to database). Can it be some SQL Server problem?

edit:application is written in VB6

Answer 1

Are you actually using the money data type or are you using a floating point type?

What happens when you use enterprise manager to select from that table? Does everything look ok?

My guess is that you are converting the data to a floating point type somewhere along the way. Probably in the ADO code.

UPDATE

Per MS: When casting money to a string type, the machine's locale comes into play. Which is why it is rounded to 2 decimal places.

You have three options.

1. First cast the money type to an equivalent decimal then cast that result to a char
2. Change the machines Regional Settings to default to the format you want.
3. Don't use the money data type to begin with, just use a decimal.

Answer 2

Don't use Enterprise Manager to draw any conclusions on what is really stored in your tables. EM has sometimes its own opinion on how to interpret data.

Looking at your CAST(....to CHAR) the reason is explained in the documentation (look for CAST & CONVERT)...

The following table shows the values for style that can be used for converting money or smallmoney to character data.

Value Output
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.

EDIT: Finally figured out how to use the BlockQuote feature. :-)