CODEWITHSUNDEEP
phpmysqldateselect
user2959620
user2959620

Reputation: 29

MySQL Select range date between two columns

I have the following table (dates) where columns are in date format as defined.

id   Column1                Column2       
 1   [2013-07-12 12:00:00]  [2013-07-14 12:00:00]

I want to extract the range like this(the dates between column1 and column2):

2013-07-12 12:00:00

2013-07-13 12:00:00

2013-07-14 12:00:00

How to make it possible?

Upvotes: 0

Views: 3585

Answers (3)

Edper
Edper

Reputation: 9322

Try this one:

SELECT * FROM DateTable
WHERE DATEDIFF('2013-07-14',dateFROM)>=0
AND DATEDIFF('2013-07-14', dateTO) <= 0

See my SQLFiddle Demo

Upvotes: 3

zsaat14
zsaat14

Reputation: 1118

Here is PHP script that will take the two dates returned from your table, separate each day, and then put that day into an array. The dates can then be easily accessed from inside the array.

<?php
    $date_1 = date("Y-m-d g:i:s", strtotime('2013-06-27 12:00:00'));
    $date_2 = date("Y-m-d g:i:s", strtotime('2013-07-03 12:00:00'));
    $results = array($date_1);
    $i = $date_1;
    while ($i <= $date_2) {
        $i = date("Y-m-d g:i:s", strtotime("+1 day", strtotime($i)));
        array_push($results, $i);
        echo $i;
    }
    print_r($results);  
?>

$date_1 and $date_2 will be the values returned from the table. Be sure you assign the earliest date to $date_1 or the script will run forever.

Upvotes: 0

Calvin
Calvin

Reputation: 710

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

Maybe do a select * where the datediff between the two columns is less than or equal to your intended range?

Upvotes: 0

Related Questions