Money Field Always Having 2 Cents Spaces 2

[Sydney1]

Hi,

Is there a way to have a Money field always contain the decimal and 2 places for the cents? Such as 100.70 instead of 100.7, or 0.00 instead of 0. Might I need to change the data type?

Thanks in advance for your help.

Sydney

 

[vongrunt]

Lemme try to open a discussion... from mathematical point of wiew, what is the difference between 100.7 and 100.70?

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[Sydney1]

Thanks for the reply.

The fields are being exported (through a DTS package)to a text file, which is then being transferred to letters that are being sent to customers. Thes letters need to have a consistent cents format.

Thanks,

Sydney

 

[jbenson001]

You can use the Convert() function. I did a quick test:

select convert(decimal(5,2),<column>)

 

[Sydney1]

Thanks guys for all your help.
I tried convert(decimal(5,2),<column>), but got an error, but then replaced the 5 with 10 and it works great.

Thanks,

Sydney

 

[jbenson001]

Ok good, mine was just an example. you need to specify the correct amount of characters. 10 is the total amount of charcters before and after the decimal.

So (10,2) will allow you to have 8places the decimal and 2 places, a total of 10

 

[Sydney1]

Hi,

I just noticed that when the origianl amount is 0 using this method it is converted to .00. Is there any way to make it 0.00?


Thanks again for all your help.

Sydney

 

[jbenson001]

Try someting like this

select <result name>= 
case 
when <col name> <> 0 then
  convert(varchar(20),convert(decimal(10,2), <col name>))
else
   '0.00'
end
 from <table>

 

[Sydney1]

I ended up using
cast(coulmnName as varchar(12)) as ColumnName.
It returns 0.00.

Thanks again for all your help. I really appreciate it.

Sydney

 

[jbenson001]

no problem..glad it works for you

 

[JamesLean]

FYI, if you use CONVERT rather than CAST for money to char conversion you can specify certain formatting options, such as whether to have commas every three digits.

DECLARE @m money
SET @m = 12345.6789

--gives 12345.68
SELECT CONVERT(varchar, @m, 0)

--gives 12,345.68
SELECT CONVERT(varchar, @m, 1)

--gives 12345.6789
SELECT CONVERT(varchar, @m, 2)

--James

 

[JamesLean]

BTW, I guess you can call that last post "just my 2 cents worth..."

;-)

--James

 

[Sydney1]

Using convert with the commas solved all my problems. Thanks so much for your help.


Sydney