Using BETWEEN operator help needed...

[bnownlater]

I am trying to use the BETWEEN operator in my sql statement, but it comes up with some wrong values...

my sql statement:
SELECT * FROM initiative WHERE Amount BETWEEN 25000000 AND 49999999

Results:
$3,000,000
$45,000,000

I strip off the '$' and replace the ',' with ''. So $3,000,000 would look like
3000000 in my mysql db. Any suggestions?

[Anon]

Looks like text-based sorting to me. What is the data type for the Amount field in the database?

[bnownlater]

varchar

[Anon]

That explains the problem. When sorting on varchar datatypes, 300 is between 2000000 and 5000000, though not numerically. Change the datatype to bigint or some other numerical one, and you will get the correct results.

[bnownlater]

Thanks.