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

Question

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

juergen d · Accepted Answer · 2013-12-13T15:27:11.943

2

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

edited Dec 13 '13 at 15:27

answered Dec 13 '13 at 15:06

juergen d

201,996
37
293
362

I've tried: 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')) between 1 and 30; but it doesn't work. – eScoo Dec 13 '13 at 15:19
It doesnt fetch any rows and gives me a error on mysql_fetch_array(). – eScoo Dec 13 '13 at 16:20
@EugenKochtyrew Then show your code, maybe we could help. Disclaimer: don't use `mysql_*` functions anymore, they are deprecated. See [Why shouldn't I use mysql_* functions in PHP?](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) for details. – Marcel Korpel Dec 13 '13 at 16:27
I've changed my form and code a little bit, now it works with your solution. Thank you so much! – eScoo Dec 16 '13 at 19:30

score 1 · Answer 2 · answered Dec 13 '13 at 15:06

1

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.

answered Dec 13 '13 at 15:06

Marc B

356,200
43
426
500

-1 "*I have to get the customers wich birthday is between 01.01. and 10.01., the year doesn't matter.*" – eggyal Dec 13 '13 at 15:07
My answer still holds. Converting to native date/datetime will make this FAR easier. – Marc B Dec 13 '13 at 15:08
3

Well, the database won't be able to do anything "automatically": you certainly cannot use `BETWEEN` in the manner shown and will need to apply further manipulations to extract day and month parts. – eggyal Dec 13 '13 at 15:09

score -1 · Answer 3 · answered Dec 13 '13 at 15:07

-1

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

answered Dec 13 '13 at 15:07

SimplyAzuma

25,214
3
23
39

Don't recommend w3schools.com as a reference. It's full of errors, see http://w3fools.com. – Marcel Korpel Dec 13 '13 at 15:10
I agree, and normally I wouldn't use it as a reference. In this case however, this article is accurate. I see no harm in referencing this particular article. – SimplyAzuma Dec 13 '13 at 15:13
Well, someone might think that other stuff on that side is ok, which it isn't. – Marcel Korpel Dec 13 '13 at 15:59
You'd think that after all the bad publicity that W3schools has been getting (for years), they would update their website in order to reflect the proper usage etc. @MarcelKorpel – Funk Forty Niner Dec 13 '13 at 16:12
@Fred-ii- Well, there used to be a list of several errors on w3fools.com with corrected stuff striked through. The last time I saw it I believe about 40% of the errors mentioned there were corrected. – Marcel Korpel Dec 13 '13 at 16:16
I'm just glad I'm not the one that built their Website. Had I built it, and as we say around here *"I'll never get work in this town again."* @MarcelKorpel – Funk Forty Niner Dec 13 '13 at 16:21

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

Edit:

3 Answers3

SQLFiddle demo