Date Range Query

[stoopkid]

I'm having some trouble collecting data between 2 different years (ie: December 2011 & January 2012).

This is a timeclock system where punches are stored as: DD-MM-YYYY (31-12-2011).

I want to be able to search for dates between: DD-MM-2011 through DD-MM-2012 but am coming up empty. When using the same year the query works just fine. Here is my failed code:

$sql = "SELECT * FROM timeclock WHERE user = 'mdloring' AND punch_in BETWEEN '26-12-2011' AND '09-01-2012' ORDER BY punch_in DESC";

There are no MySQL errors, so I think it has something to do with the way I store my dates. I know that MySQL's default date storage is YYYY-MM-DD but I rarely use that.

Any help is greatly appreciated.

[blocblue]

What data type is your date field? Varchar?

One reason MySQL stores dates in the format YYYY-MM-DD is that it allows the field values to be quickly searched. For example, if searching for values between 26/12/2011 and 09/01/2012, MySQL would be able to discard all fields that start with anything other than 2011 or 2012.

With your approach, MySQL will likely need to check the entire value of every field.

Why are you not using the MySQL Date Time field format?

[stoopkid]

I am working off of an old project, and instead of having to re-write the entire system, I am making edits to the current one.

Generally I tend to store all date values as unix timestamps (strtotime) and never use MySQL's Date Time format.

Problem solved:

$sql = "SELECT * FROM timeclock WHERE user = 'mdloring' AND punch_in BETWEEN '26-12-2011' AND '9-01-2012' ORDER BY punch_in DESC";

By simply removing the leading 0 from 09, the query worked just fine. Wahoo!