money data-type query

I have a database table with a field of datatype "money".
I need to have this populated with rows that only have data up to a maximum
of two decimal places (i.e. pounds and pence).
However, I have come across some rows that have up to 3 significant figures
after the decimal points (i.e. 1/10th of a penny).
I need to identify all the rows that have more than two significant numbers
after the decimal place.
I thought:
SELECT * FROM myTable where
CAST(myField AS VARCHAR(20)) LIKE '%.[0-9][0-9][1-9]%'
would do the trick.
It doesn't.
Any suggestions would be really appreciated!
Thanks
GriffSorted:
WHERE (myField<> round(myField,2))
Griff|||"Griff" <Howling@.The.Moon> wrote in message
news:%23KcVFIIEGHA.140@.TK2MSFTNGP12.phx.gbl...
>I have a database table with a field of datatype "money".
> I need to have this populated with rows that only have data up to a
> maximum of two decimal places (i.e. pounds and pence).
> However, I have come across some rows that have up to 3 significant
> figures after the decimal points (i.e. 1/10th of a penny).
> I need to identify all the rows that have more than two significant
> numbers after the decimal place.
> I thought:
> SELECT * FROM myTable where
> CAST(myField AS VARCHAR(20)) LIKE '%.[0-9][0-9][1-9]%'
> would do the trick.
> It doesn't.
> Any suggestions would be really appreciated!
> Thanks
> Griff
>
Try this:
SELECT x
FROM tbl
WHERE x<>ROUND(x,2,1);
Be very careful when using MONEY for monetary amounts. In my opinion the
rounding errors caused by MONEY make DECIMAL a much better choice for
currency values in every case. See the example below. Do you have a good
excuse for using MONEY?
DECLARE
@.mon1 MONEY,
@.mon2 MONEY,
@.mon3 MONEY,
@.mon4 MONEY,
@.num1 DECIMAL(19,4),
@.num2 DECIMAL(19,4),
@.num3 DECIMAL(19,4),
@.num4 DECIMAL(19,4) ;
SELECT
@.mon1 = 100, @.mon2 = 339, @.mon3 = 10000,
@.num1 = 100, @.num2 = 339, @.num3 = 10000 ;
SET @.mon4 = @.mon1/@.mon2*@.mon3 ;
SET @.num4 = @.num1/@.num2*@.num3 ;
SELECT @.mon4 AS money_result,
@.num4 AS decimal_result ;
Result:
money_result decimal_result
-- --
2949.0000 2949.8525
(1 row(s) affected)
David Portas
SQL Server MVP
--|||The money data type stores data upto 4 decimal places. If you only require 2
decimal places you could consider storing the data as decimal or numeric
instead
HTH. Ryan
"Griff" <Howling@.The.Moon> wrote in message
news:OjWSPMIEGHA.344@.TK2MSFTNGP11.phx.gbl...
> Sorted:
> WHERE (myField<> round(myField,2))
> Griff
>