MySQL Date Range select problem


#1

I?m trying to shoehorn some functionality onto an application that is used for keeping track of time spent on projects. I?m also not the original developer, so I don?t know why something?s were done the way they are. This application uses 3 columns in the MySQL database (Month, Day, Year) instead of the usual timestamp or datetime. I?m having a problem doing a SQL query that pulls up records between two dates that are selected from an HTML form. How should I write my SQL query if I wanted to select everything from January 1st 2006 (Month =1 Day = 1 Year = 2006) to March 30th 2006 (Month = 3 Day =30 Year = 2006)?


#2

Given the information you provided, I would suggest…

SELECT * WHERE month <4 AND year = 2006;

Since there are no months LESS than 1 there is no need to create a lower bound for the query. Months 1,2, and 3 (Jan, Feb, and March) are all less than 4 and the year for all of them is 2006. You are requesting EVERYTHING for each month, so there is no need to qualify the query with the DAY.

Hope this helps.