Using Max and Min with the Money datatype

[HIC]

When I try to get the max and min of a column with a money datatype it brings back 1000 as min and 999 as max. Obviously, I need 1000 to be the max. I have tried to cast and convert this to different datatypes, but have been unable to make this work properly. Any ideas?

Thanks!

HIC

 

[cmmrfrds]

Are you sure the 1000 is not a negative number and you are stripping off the sign somehow?

Show a copy of your SQL statement.

Have you verified through Query Analyzer or other means the actual low and high values in the table?

 

[HIC]

Thanks for the reply

I have a table of 10 rows. All have either a 999 or 1000 in a field called Money.

Select Emp_id, min(Money)as minimum, max(Money) as maximum from testtable where emp_id=3456
group by emp_id

 

[cmmrfrds]

It should work are you sure you are not reversing the names min and max in the AS definition.

I tried several scenarios and always got the expected results. Is this sql server 2000?

 

[HIC]

SQL 7

 

[sagn]

Are you SURE the *data type* is MONEY (not just the column name!)? and not
CHAR?

If it is a char type 1000 is indeed less than 999

declare @a char(4)
declare @b char(4)

set @a = '1000'
set @b = '999'

if (@a < @b)
print 'less'

 

[bperry]

I would like to hear how this issue turned out.
I thought cmmrfrd's and sagn's suggestions were pretty good.