Jack Bramhall Jack Bramhall - 2 years ago 97
MySQL Question

Select between dates from table - strtotime?

I have been trying for a while, read countless stackoverflow answers and still cant crack it!

I have a table in my db with a field called dob. This field is currently just a TEXT field (but i have since tried changing it to a DATE field and still cant get it to work).

The DOB field's data is in this format (UK dates) - 22/05/2016.

Im trying to find out the number of users who's birthdays are between two dates.

For example, anyone who was born in the last two years:

$twoyearsago=date('d/m/Y', strtotime("-2 years"));
$today = date("d/m/Y");

$sql = mysql_query("SELECT * FROM users WHERE dob >= '" . $twoyearsago . "' AND date <= '" . $today . "' ORDER by id DESC");

I also tried:

$sql = mysql_query("SELECT * FROM users WHERE dob BETWEEN '" . date('d-m-Y', strtotime($twoyearsago)) . "' AND '" . date('d-m-Y', strtotime($today)) . "'";

Hopefully you can see where me logic is and hoping you will see where im going wrong - any help would be appreciated.


fvu fvu
Answer Source

The problem with many local date formats is that their lexical and chronological order are different (eg, 16-11-2016 comes after 11-12-2016 lexically, but before chronologically). That's why storing dates in string fields in some regional format is in most cases a bad idea: you will get sorting issues sooner or later.

Next, when specifying dates literally for MySQL, you have to respect certain formats, as explained in the documentation

Putting that into practice, the range variables should look something like this:

$today = date("Y-m-d");
$twoyearsago=date("Y-m-d", strtotime("-2 years"));

Then we use a built-in function str_to_date to convert the string column into a date that can be compared correctly:

STR_TO_DATE(dob, '%d/%m/%Y') between '$twoyearsago' and '$today'

This will work, but in the long run you're much better off converting that dob column into a real date format (as @BerndBuffen shows) as it's clearer, easier to internationalize and a lot better performing.

Sidenote: you are still using the long-deprecated mysql_ extension. You should really switch to either mysqli_ or PDO.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download