Money data type question

[Killermove]

I'm using the money data type in a field - but (obvious to me) I need the data to contain two decimal places NO MATTER WHAT - even if they are zeros!

I keep putting in "5.20" and I get "5.2".

How do I keep this from happening? I need both decimal places! I thought about using a text field - but that seems to be wasteful and I would have to do a type cast to do numeric computations.

There has to be a way to do this - this IS the purpose of the data type, isn't it??

Thanks for any help!!

Ryan

[Claire]

Instead of defining it as money datatype,change it to decimal(8,2) or decimal(5,2) depeneds on how large your inserted values are

If it's more than 1000,then define it
decimal(6,2)
or
decimal(7,2)
.
.
.

[Killermove]

Originally posted by Claire
Instead of defining it as money datatype,change it to decimal(8,2) or decimal(5,2) depeneds on how large your inserted values are

If it's more than 1000,then define it
decimal(6,2)
or
decimal(7,2)
.
.
.

Thanks Claire - but I have a followup question:

Thanks for the suggestion. I created a new column with a decimal data type (9,2) to test. It still drop the cents postion if it is zero!

Am I doing it wrong?

-Ryan

[Claire]

I dont think you are doing anything wrong.Here is an example to create a table with column decimal(9,2)

create table tb1 (col1 decimal(9,2))
insert into tb1 select 0
insert into tb1 select 13.2
insert into tb1 select 12.5
go
SELECT * FROM TB1
go


The result is as followed
col1
-----------
.00
13.20
12.50

Did you do the same thing?

[Killermove]

Originally posted by Claire
The result is as followed
col1
-----------
.00
13.20
12.50

Did you do the same thing? [/B]

No. I get:

col1
---------
0
13.2
12.5


I'm using SQL Server Enterprise Manager and on SQL 2000 database. I keep double checking to make usr eI have ti set right, I can't imagine what I'm missing!

I have decimal data type with precision set to 9 and scale set to 2. I save the talbe changes, update a value and it still shows the same!

Ryan

[Killermove]

...bump...



Anybody??


Ryan

[skhanal]

What tool are you using to query the table?. Is it query analyzer?

If you open the table from Enterprise Manager, do you see correct values?

[Killermove]

Originally posted by skhanal
What tool are you using to query the table?. Is it query analyzer?

If you open the table from Enterprise Manager, do you see correct values?

I'm actually editing the values in Enterprise Manager (and/or via a web page) and viewing the values via a webpage (asp) and through Etnerprise Manager (after I enter the data) and using a query on the table.

Ryan

[Killermove]

Originally posted by Killermove


I'm actually editing the values in Enterprise Manager (and/or via a web page) and viewing the values via a webpage (asp) and through Etnerprise Manager (after I enter the data) and using a query on the table.

Ryan

I just checked in QA and they come up corectly. So is it ASP that is dropping the zero's?

Ryan

[MAK]

Check your ODBC connection settings. It may be using regional settings for currency, dates, numbers and time.

[Rawhide]

If you're displaying it via ASP, why not use the FormatCurrency function.

Response.Write FormatCurrency(RS("MoneyField"), 2)


The second value is the number of decimal points to show.

FormatCurrency will add the denomination symbol to it. If you don't want that to happen, use FormatNumber instead.

[Killermove]

Originally posted by Rawhide
If you're displaying it via ASP, why not use the FormatCurrency function.

Response.Write FormatCurrency(RS("MoneyField"), 2)


The second value is the number of decimal points to show.

FormatCurrency will add the denomination symbol to it. If you don't want that to happen, use FormatNumber instead.

Perfect - thats exactly what I needed! I didn't know about the FormatCurrency function.

THANK YOU!!!

Ryan