Results Unexpected

treeleaf20

I have the following query: Select * from bar_events where date BETWEEN '02/26/2006' AND '03/04/2006'

For some reason it selects an event that is scheduled on 02/26/2007, why does it do that?

Thanks in advance.

tomhath

What type is the "date" column?

If it's a string, then the query is doing what you asked (find a string between '02/26/2006' AND '03/04/2006').

treeleaf20

It's VARCHAR, what type should I use. I went with that becasue of the slashes.

rincewind456

It should be a DATE or DATETIME field type and the data should be saved in the format "YYYY-MM-DD" or "YYYY-MM-DD HH:MM:SS" you can then use MySQL's DATE_FORMAT() function to manipulate the output to any way you want, like

DATE_FORMAT('2006-02-26' '%m/%d/%y')

will out put

02/26/2006

, and also be able to do the comparisons you want.