sql server trimming 0's from money field

[monkle]

I am using Microsoft SQL Server.

I have a money field in the database. When I try to store decimal values, it trims the trailing zeros, for example:

2.50 becomes 2.5

2.00 becomes 2

I have to have an exact amount, out to two decimal places, every time. Is there a way to force sql server to save that information, or will I have to make a script to automatically append 0's to the end, out to two decimal places past the '.'?

 

[JayKusch]

Actually the Money data type has 4 decimal places. You could try ...

CONVERT(Money,MyDecimalValue)

Thanks

J. Kusch

 

[monkle]

Thank you. I'm running php scripts against the database, both to insert values and when I pull the values. I see the problem is in the php typecasting.

 

[Rechmali]

Hi,
Hope you don't mind me hijacking the thread, but i seem to be getting exactly the same problem, but at server level.

I am trying to store 1.80, not 1.8, but when I edit the field direct through enterprise manager it keeps losing the trailing zero.

Where am I going wrong?

Hope you can help,

Thanks,
Chris

 

[NathanGriffiths]

Hi guys,

I think the problem is in the way your data is displayed and not in the way it is stored. SQL Server does not 'trim' trailing zeros but depending on how you are viewing the data you might see it that way. Enterprise Manager for example trims trailing zeros from displayed data by default, although the zeros are stored in the database.

see tbroadbent's faq183-3007 for more explanation.

Nathan

----------------------------------------
Want to get a good response to your question? Read this FAQ! -> faq183-874
----------------------------------------

 

[mrdenny]

When viewing data, try using Query Analyser. It will display the data with out trimming the data (stuff like hidding the trailing 0s). Enterprise manager was designed as a server admin tool, not a data editing tool.

In ASP (i'm assuming that PHP has this same function) there is a function called formatcurrency() which will take the data storred in the SQL Server and convert it to a currency looking value includding commas, periods, and currency symbols as defined by the region of the OS.

Denny

--Anything is possible. All it takes is a little research. (Me)