Frank,
I went through the same learning experience, though I was trying to use a Month column as an integer.
Partitioning by month would be a nice feature for a future release.
What I finally wound up using was the example pointed out in:
http://datacharmer.blogspot.com/2008/12/partition-helper-improving-usability.html
I did not use his Perl code, I just used a good text editor to create all the partition definitions.
In my table, Quote_Date is defined as type DATE and the partition definitions read:
PARTITION BY RANGE (to_days(Quote_Date))
(
Partition p000000 VALUES LESS THAN (to_days('2002-01-01')),
Partition p200201 VALUES LESS THAN (to_days('2002-02-01')),
Partition p200202 VALUES LESS THAN (to_days('2002-03-01')),
Partition p200203 VALUES LESS THAN (to_days('2002-04-01')),
Partition p200204 VALUES LESS THAN (to_days('2002-05-01')),
.......
Partition p201108 VALUES LESS THAN (to_days('2011-09-01')),
Partition p201109 VALUES LESS THAN (to_days('2011-10-01')),
Partition p201110 VALUES LESS THAN (to_days('2011-11-01')),
Partition p201111 VALUES LESS THAN (to_days('2011-12-01')),
Partition p201112 VALUES LESS THAN (to_days('2012-01-01')),
Partition p999999 VALUES LESS THAN MAXVALUE
)
I am not expecting dates before 2002 or after 2011, but I have defined partitions p000000 and p999999 to catch outliers.
I generally use the BETWEEN clause, just as you did, in my SELECTs and the EXPLAINs show the single partitions being used.
Allan