So I have an existing database system that is using the date for customer records in a TEXT format. The format being used is:
MM/DD/YYYY
What I am wanting to do is finish my reporting system but need to query records in a MySQL query between a date range.
What is the most effective way to do this without changing the entire database and the thousands of records?
Rephrase, I basically want a query to select X number of records if their date is between start date MM/DD/YYYY and end date MM/DD/YYYY.
Thoughts?
Thanks in advance.
PHP Date range query
Moderator: General Moderators
- AbraCadaver
- DevNet Master
- Posts: 2572
- Joined: Mon Feb 24, 2003 10:12 am
- Location: The Republic of Texas
- Contact:
Re: PHP Date range query
Assuming you have $start_date and $end_date in the YYYY-MM-DD format (easy to do with date()) then something like this may work (not tested):
If that doesn't work, then in PHP you're going to have to pull all records and when you loop through them do a strtotime() on the date_field and compare (assuming $start_date and $end_date are unix timestamps). Something like:
It really shouldn't be that hard to modify the DB though. You just add a new field of type 'DATE', with PHP query all rows, loop through and strtotime() and date() format the old date and insert into the new field. Then you can begin using the new field, or delete the old field and rename the new field to the old one.
HTH
-Shawn
Code: Select all
SELECT * FROM table_name WHERE
CAST(CONCAT_WS('-', RIGHT(date_field, 4), LEFT(date_field, 2), MID(date_field, 4, 2))) AS DATE
BETWEEN $start_date AND $end_dateCode: Select all
while($row = mysql_fetch_array($result)) {
$date = strtotime($row['date_field']);
if($date >= $start_date && $date <= $end_date) {
$in_range[] = $row;
}
}HTH
-Shawn
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.