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?
Try this one:
SELECT * FROM DateTable
WHERE DATEDIFF('2013-07-14',dateFROM)>=0
AND DATEDIFF('2013-07-14', dateTO) <= 0
See my SQLFiddle Demo
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.
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?
$resultline doesn't make any sense. Are you expecting 3 columns for that 1 record or is that a string?