8

I'm trying to write a query that will check today's date against my table columns date1 and date2 in mysql/php.. This is what I'm after:

'events' table:

  • date1 = start date (XXXX-XX-XX)
  • date2 = end date (XXXX-XX-XX)

query:

  • select * from events where 2012-01-18 between date1 and date2 (or equal to date1 and date2)

But I'm not sure how to go about it.. any help would be appreciated :)

EDIT:

Maybe I was unclear.. if todays date = '2012-01-18' I need it to find results if today's date is between the date range of date1 and date2.. So date1 may be '2012-01-04' and date2 may be '2012-01-21'.. so if todays date falls between or on those dates, a result is returned..

Rishabh
  • 3,752
  • 4
  • 47
  • 74
SoulieBaby
  • 5,405
  • 25
  • 95
  • 145

6 Answers6

18
SELECT * FROM events 
  WHERE date1<='2012-01-18'
  AND date2>='2012-01-18'
Eugen Rieck
  • 64,175
  • 10
  • 70
  • 92
  • I need it to show if 2012-01-18 is between the range of date1 and date2 if that makes sense? – SoulieBaby Jan 18 '12 at 03:03
  • Which is exactly what this query does: It selects all rows, where 2012-01-18 is inside the range defined in the row – Eugen Rieck Jan 18 '12 at 03:05
  • hmm this is my query (changed the date to be between an event in the dbase) SELECT * FROM events WHERE date1 >= '2012-04-01' AND date2 <= '2012-04-01' but no results are returned.. the event which it should pull out date1 = 2012-03-30 and date2 = 2012-04-16... – SoulieBaby Jan 18 '12 at 03:09
  • nevermind i had my greater than/less than the wrong way around lol – SoulieBaby Jan 18 '12 at 03:11
  • 1
    You have the comparisons wrong: You have "date1 >= '2012-04-01' AND date2 <= '2012-04-01' " but you need "date1 <= '2012-04-01' AND date2 >= '2012-04-01' " - see my answer – Eugen Rieck Jan 18 '12 at 03:13
  • Converting the BETWEEN to 2 different comparisons is good, if the arguments to BETWEEN are non-constant – Eugen Rieck Jan 18 '12 at 03:14
2

If your referring to compare the date today is between a start and end date, I think you should use this:

SELECT *
FROM table
WHERE '2014-08-20' >= start_date
AND '2014-08-20' <= end_date

Hope this helps :)

syn
  • 341
  • 3
  • 6
1
SELECT *
FROM events
WHERE date1 <= '2012-01-18'
AND date2 >= '2012-01-18';

This should get you started. You can use DATE(NOW()) to get today's date if you don't want to hardcode a date.

styfle
  • 22,361
  • 27
  • 86
  • 128
  • this post does not make any sense – Tim Jun 20 '12 at 00:37
  • @Tim This comment does not make any sense. – styfle Jun 20 '12 at 17:07
  • This query would evaluate to "SELECT * FROM events WHERE '2010-01-01' <= '2012-01-18' AND '2015-01-01' >= '2012-01-18';" which will return all entries from the 'events' table - rather than querying for events where date1 and date2 are greater than $date. Thanks – Tim Jun 25 '12 at 06:28
1

Try this,

SELECT * FROM events  
  WHERE date1<='2012-01-19'  
  AND date2>='2012-01-18'
Jobin
  • 8,238
  • 1
  • 33
  • 52
emj365
  • 2,028
  • 2
  • 19
  • 24
0

Though there are many answers available to this question I would like to give my response regarding the same situation I faced.

I am using php to query mysql database.

first what I do is to convert the date in the mysql supported date format which is yyyy-mm-dd

$date = new DateTime($date);
$date=date_format($date, 'Y-m-d');

in the query's where clause I use BETWEEN

"'$date' BETWEEN start_date AND end_date"

this works perfectly given the case described here.

Rishabh
  • 3,752
  • 4
  • 47
  • 74
-2

Modified version of styfle's code

$date1 = '2010-01-01'; // example min
$date2 = '2015-01-01'; // example max
$sql = "SELECT * FROM events WHERE $date1 >= '2012-01-18' AND $date2 <= '2012-01-18';";
$result = mysql_query($sql) or die(mysql_error());
if (mysql_num_rows($result) != 0) {
    // This date is equal to or within the range specified
} else {
    // The date was not within the range specified
}

then you can have code executed based on the result

Silvertiger
  • 1,680
  • 2
  • 19
  • 32