Money data type - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Money data type

Author

Topic

jess
Starting Member

17 Posts

Posted - 2007-08-16 : 10:23:29

Hi, I have a question about the Money data type.

Sometimes there are fractions of pennies out there in our database, and I want to find all of those fractions. Basically I want to find all of the entries in a table that have a value other than 0 in the 3rd digit to the right of the decimal place. (For example, the 1 in the value $5.0010)

Can anyone help me do this?

Here's some sample code for a table with 3 entries. 2 of the entries have fractions of pennies, so that is what I want the end result to return.

DECLARE @amounts TABLE
(
id int,
value money
)

INSERT INTO @amounts
SELECT 1, 5.0010
INSERT INTO @amounts
SELECT 2, 3.3500
INSERT INTO @amounts
SELECT 3, 6.0010

What I need is some sort of statement to pull the values that have fractions. Like:

SELECT *
FROM @amounts
WHERE --?? Value in the 3rd place to the right of the decimal is not 0

The results should then be:

id value
1 5.0010
3 6.0010

Any help would be very much appreciated!!!

Thanks!
Jess

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-08-16 : 10:26:17

[code]SELECT *
FROM @amounts
WHERE CONVERT(int, value * 1000) % 10 <> 0[/code]

KH
[spoiler]Time is always against us[/spoiler]

jess
Starting Member

17 Posts

Posted - 2007-08-16 : 10:34:28

Genius! That is exactly what I needed.
Many thanks!
Jess

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources