SQL table column won't format decimals correctly

[johncassell]

Hello,

I have a table with a column I would like to use for peoples hourly pay. It would never be more than £99.99 and obviously would never go into 3 decimal places.

In my table i have specified the format as smallmoney and directly entered 10.52 - it stored as 10.5200.

I then changed format to numeric(2,2) which also stored it as 10.5200.

How can I force it to be 2 decimal places?

Thanks

John

 

[Cavar]

From what I see when adding a new money/smallmoney column to a SQL table via Enterprise manager, the default scale is always 4 and won't let you change it.

I wouldn't worry about it, just format the output in code and ensure that your code only allows 2 decimals places by rounding.

CT

 

[johncassell]

Hi Cavar,

I thought it would be an easy fix but no big deal if it's not possible, I will just format with the output code.

Thanks for the reply.

John

 

[cjard]

I then changed format to numeric(2,2) which also stored it as 10.5200.

How can I force it to be 2 decimal places?

Thanks

John

I think you'll find it should be a decimal(4, 2)

"only decimal has precision and scale you can set" [the rest are dictat by M$] - http://doc.ddart.net/mssql/sql70/da-db_3.htm

decimal(4, 2) = decimal 4 digits total, 2 of which are dec pl

 

[johncassell]

Thanks cjard,

actually worked out my mistake later on but appreciate the reply nonetheless.

thanks

john