Query Date Range

[Canman2005]

Hi all

 

I have a table which holds a bunch of dates

 

id  datefrom      dateto

1    2007-02-22  2007-03-05

2    2007-05-13  2007-06-23

3    2007-05-25  2007-07-23

 

the `datefrom` and `dateto` are dates in the format YYYY-MM-DD, the `datefrom` means the date the event starts and the `dateto` means the date the event ends.

 

What I want to run a query on this table and return any rows which have todays date between the 'datefrom' and the 'dateto'.

 

Does that make much sense?

 

Can anyone help?

 

Thanks in advance

 

Dave

[Wuhtzu]

If your times were unix timestamps you would be doing this:

 

$today = time();

mysql_query("SELECT * FROM table WHERE datefrom < " . $today . " AND " . $today . "< dateto");

 

Now your only problem is that you haven't got timestamps but some other dateformat. You could play around with PHP's strtotime() function:

 

http://no.php.net/strtotime

 

but I don't know if there is a smarter way.

 

 

[Barand]

SELECT * FROM table WHERE CURDATE() BETWEEN datefrom AND dateto

[Wuhtzu]

Go for Barand's solution - BETWEEN is obviously smart and yyyy-mm-dd appears to be the date format of mysql

 

I always use timestamps my self, so I would never have thought of the solution Barand did

[Canman2005]

Thanks everyone, that seemed to work fine.

 

Can anyone tell me, I want to also return any rows which also start on a monday of the date that is being returned, so basically I can return things that also start that week?

 

Does that make sense?

 

Thanks

 

Dave

[dough boy]

# Select all events that occur between a range or start on a monday
SELECT * 
FROM table 
WHERE (
NOW() BETWEEN datefrom AND dateto 
OR DAYOFWEEK(datefrom) = 2
)

# Select all events that occur between a range AND start on a monday
SELECT * 
FROM table 
WHERE (
NOW() BETWEEN datefrom AND dateto 
AND DAYOFWEEK(datefrom) = 2
)

 

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

[Barand]

<?php
$mon =  date('Y-m-d', strtotime('this monday'));
$fri =  date('Y-m-d', strtotime('this friday'));

$sql = "SELECT * FROM table 
        WHERE (datefrom BETWEEN '$mon' AND '$fri')
        OR (CURDATE() BETWEEN datefrom AND dateto) 
        ";
?>