I've got a problem with mysql getting the rows between two dates.
The column is res_birthday (VARCHAR) and the date looks like 30.11.1970.
Now I have to get customers whose birthdays are between 01.01. and 10.01., the year doesn't matter.
I've tried many solutions with:
STR_TO_DATE(`res_birthday`, '%d.%m')
but I cannot compare it to the start and end date.
Any idea how I can solve this?
Here is some code with the solutions mentioned below, but it does not work.
$query = mysql_query("SELECT * FROM `jtmpl2_chronoforms_data_test_reservierung` WHERE MONTH(STR_TO_DATE(`res_birthday`, '%d.%m.%y')) = 5 AND DAYOFMONTH(STR_TO_DATE(`res_birthday`, '%d.%m.%y')) = 10;");
while($info = mysql_fetch_array( $query ))
function createCodeOrg($query){
while($info = mysql_fetch_array( $query ))
{
$code = '<tr>
<td>'.$info['res_anrede'].'</td>
<td>'.ucfirst($info['res_vorname']).'</td>
<td>'.ucfirst($info['res_nachname']).'</td>
<td>'.ucfirst($info['res_strasse_hnr']).'</td>
<td>'.$info['res_plz'].'</td>
<td>'.ucfirst($info['res_ort']).'</td>
<td>'.$info['res_geburtstag'].'</td>
<td><a href="mailto:'.$info['res_email'].'">'.$info['res_email'].'</a></td>
</tr>';
echo $code;
}
}
select * from your_table
where MONTH(STR_TO_DATE(res_birthday, '%d.%m.%Y')) = 1
and DAYOFMONTH(STR_TO_DATE(res_birthday, '%d.%m.%Y')) between 1 and 10
Your problem is you're storing dates as varchars. This removes the ability of the DB to what it could automatically if you'd been using DATE or DATETIME fields:
SELECT ...
FROM ...
WHERE res_birthday BETWEEN 'yyyy-mm-dd' AND 'yyyy-mm-dd'
Convert your fields now, and your problem essentially goes away.
I believe this is what you are looking for:
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;