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-13 15:27:11Z

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, 2013 at 15:27

answered Dec 13, 2013 at 15:06

juergen d

205k40 gold badges303 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

eScoo Over a year ago

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 Over a year ago

It doesnt fetch any rows and gives me a error on mysql_fetch_array().

Marcel Korpel Over a year ago

@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? for details.

eScoo Over a year ago

I've changed my form and code a little bit, now it works with your solution. Thank you so much!

Marc B · Accepted Answer · 2013-12-13 15:06:13Z

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, 2013 at 15:06

Marc B

362k44 gold badges433 silver badges508 bronze badges

3 Comments

eggyal Over a year ago

-1 "I have to get the customers wich birthday is between 01.01. and 10.01., the year doesn't matter."

Marc B Over a year ago

My answer still holds. Converting to native date/datetime will make this FAR easier.

eggyal Over a year ago

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.

SimplyAzuma · Accepted Answer · 2013-12-13 15:07:08Z

-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, 2013 at 15:07

SimplyAzuma

25.7k3 gold badges26 silver badges39 bronze badges

6 Comments

Marcel Korpel Over a year ago

Don't recommend w3schools.com as a reference. It's full of errors, see w3fools.com.

SimplyAzuma Over a year ago

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.

Marcel Korpel Over a year ago

Well, someone might think that other stuff on that side is ok, which it isn't.

Funk Forty Niner Over a year ago

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

Marcel Korpel Over a year ago

@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.

|

Collectives™ on Stack Overflow

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

Edit:

3 Answers 3

SQLFiddle demo

4 Comments

3 Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Edit:

3 Answers 3

4 Comments

3 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related