CODEWITHSUNDEEP
phpmysql
eScoo
eScoo

Reputation: 151

PHP - MySQL: Get rows from table where date between two dates

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?

Edit:

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;
    } 
}

Upvotes: 0

Views: 1170

Answers (3)

juergen d
juergen d

Reputation: 204924

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

SQLFiddle demo

Upvotes: 2

SimplyAzuma
SimplyAzuma

Reputation: 25604

I believe this is what you are looking for:

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;

Source: http://www.w3schools.com/sql/sql_between.asp

Upvotes: -1

Marc B
Marc B

Reputation: 360902

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.

Upvotes: 1

Related Questions