Retrieving money values as integers

[croydon]

I need to extract the dollar and cents portions of an amount as separate integer values. I can't find a method of doing this.

Any assistance would be appreciated.

 

[bborissov]

SELECT CAST(MyMoneyField as int) AS DolarPortion,
       CAST((MyMoneyField-CAST(MyMoneyField as int))*100 as int) AS CentsPortion

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[croydon]

Borislav, thanks for your reply.

Can the Cast be run on a SUMmed amount and returned as a parameter like this?

@dollars = CAST(SUM(dbo.tblTreatment.PaymentChequeValuePaid) as int)

I get a syntax error.

Thanks.

 

[bborissov]

DECLARE @dollars int
DECLARE @cents   int
SELECT @dollars = SUM(CAST(dbo.tblTreatment.PaymentChequeValuePaid as int)),
       @cents   = SUM(CAST((dbo.tblTreatment.PaymentChequeValuePaid-CAST(dbo.tblTreatment.PaymentChequeValuePaid as int))*100 as int))
FROM dbo.tblTreatment

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[croydon]

Borislav, that's great. Thanks again for your help.

 

[croydon]

This is almost working perfectly. The only problem I have now seems to be Rounding.

The line of SQL code is:

@cents = SUM(CAST((dbo.tblTreatment.ChequeValue-CAST(dbo.tblTreatment.ChequeValue as int))*100 as int)),

An example of the problem is:

235.63 + 5689.23 = 5924.86. But @cents = 84

I have tried the ROUND function but I cannot get this to work. Can you help? Thanks.

 

[earthandfire]

I find PARSENAME perfect for this type of calculation:

Table6 in this contains one field ActualAmount (datatype money) with the following:

[tt]
ActualAmount
1.2300
2.3400
3.4500
4.5600
[/tt]

then using the following:

[tt]
select sum(actualamount) from table6
select
parsename(convert(float, sum(actualamount)), 2) as dollars,
parsename(convert(float, sum(actualamount)), 1) as cents
from table6

or if you prefer

select
convert(int, parsename(convert(float, sum(actualamount)), 2)) as dollars,
convert(int, parsename(convert(float, sum(actualamount)), 1)) as cents
from table6
[/tt]

the output is:

[tt]
11.5800
[/tt]

and

[tt]
dollars cents
11 58
[/tt]



Hope this helps.

 

[croydon]

earthandfire, thanks for your suggestion.

My dollars amount is now correct, but unfortunately my cents appears to be rounding up. The following are the amounts in the table:

1124.38
8549.85
1234.56
1254.98
--------
12163.77

The dollars correctly shows 12163 but the cents result is 8 (I assume this is 77 rounding up). I have tried both methods with the same result.

Thanks again.