BETWEEN With smallmoney 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

SQL Server Development (2000)

BETWEEN With smallmoney Type

Author

Topic

CactusJuice
Starting Member

46 Posts

Posted - 2003-02-13 : 17:41:01

I have a column AdPrice which is type smallmoney. When I do a select using a BETWEEN the high end filters out but not the low end.

--Test Values
SET @Min = 1000
SET @Max = 5000

SELECT AdPrice ... WHERE AdPrice BETWEEN @Min AND @Max

Using the above test values I don't get any ads back that are over $5000. Great. But I do get back everything below $1000.

I have tried passing the values from the web page as smallmoney and as int. I've also tried CAST to int from within the sproc. I've even tried using >= AND <=.

This is freaking me out.

What am I missing?

thanks,

Cameron

nr
SQLTeam MVY

12543 Posts

Posted - 2003-02-13 : 17:46:27

I suspect there is something wrong with your code somewhere.
Likely you are setting @min to 0

try this

create table #a(i smallmoney)
insert #a select 5
insert #a select 50
insert #a select 500
insert #a select 1000
insert #a select 1500
insert #a select 5000
insert #a select 50000

declare @min smallmoney, @max smallmoney
select @min = 1000, @max = 5000

select * from #a where i between @min and @max

drop table #a

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

CactusJuice
Starting Member

46 Posts

Posted - 2003-02-13 : 18:50:37

quote:

I suspect there is something wrong with your code somewhere.

OK thanks. I ran your example and it worked fine so obviously I've got something hosed in my overall code. At least I know I am using BETWEEN correctly! Just need to find my error.

cheers,

Cameron -- (eop)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources