Searching by date range

Question

I am trying to create a search criteria that is tied into a mySQL DB, by date range, but it does not seem to return expected results so far.

The dates are stored in the following format: mm/dd/yy and I would simply like to sort by date range, so if the user would enters the dates From: 2/2/12 to <current date> than the query will return all queries the fall in that date range including the current date.

This is what I tried:

I have testing workorder records in the DB with the following timestamps: 1362250990 and 1362251039.

PHP

$date1 = strtotime($_GET['dateFirst']);
$date2 = strtotime($_GET['dateSecond']);
echo $date1 . '> work order date <='. $date2;
$results = mysqli_query($mysqli, "SELECT * FROM workorders WHERE (work_order_date > $date1) AND (work_order_date <= $date2) LIMIT 20");

Answer 1

Dates should ALWAYS be stored in a suitable column type (DATE, DATETIME, or preferably TIMESTAMP).

This automatically makes them easily sortable, since they are in big-endian order.

Assuming your input formats are reliable, you can then use the BETWEEN comparison operator like this:

$date1 = date("Y-m-d H:i:s",$_GET['dateFirst']);
$date2 = date("Y-m-d H:i:s",$_GET['dateSecond']);
$results = mysql_query("SELECT * FROM `workorders` WHERE `work_order_date` BETWEEN '$date1' AND '$date2'");