Retrieve Rows with three numbers after decimal poi

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)

Retrieve Rows with three numbers after decimal poi

Author

Topic

ewebb02
Starting Member

6 Posts

Posted - 2006-06-08 : 09:05:36

I have a table that I need to clean up. The values should be money and be something like 32.33 or 15.98; however some of them are being returned as 32.331 or 15.984. I am trying to see all that is effected by these numbers and would like to isolate the rows that have them, but can't think of the best way to do that.

Any help would be greatly appreciated!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-08 : 09:12:56

[code]declare @table table
(
val money
)

insert into @table
select 32.33 union all
select 15.98 union all
select 32.331 union all
select 15.984

select val
from @table
where convert(bigint, val * 10000) % 100 <> 0[/code]

ewebb02
Starting Member

6 Posts

Posted - 2006-06-08 : 09:20:40

Perfect...Thank you so much!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-08 : 11:15:08

quote:

Originally posted by khtan

declare @table table
(
	val	money
)

insert into @table
select 32.33 	union all
select 15.98 	union all
select 32.331	union all
select 15.984

select	val
from	@table
where	convert(bigint, val * 10000) % 100 <> 0

This seems easier to code:


select
	val
from
	@table
where
	round(val,2) <> val

CODO ERGO SUM

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-06-08 : 11:17:10

"This seems easier to code"
Indeed.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources