between date help

[dadamssg]

I'm have events stored in my database with start and stop datetimes. I am trying to write a sql query where i pull events occur on a particular day. The event could start on that day, end on that day, OR start before the day and end after the day. Any help would be much appreciated!

[fenway]

What's wrong with BETWEEN?

[dadamssg]

i'm sure BETWEEN would work. i don't know the correct syntax to use though. I'm now familiar with querying with dates.

 

i have something like the following but its not workin...

$date = "2010-11-25";

$query = "SELECT * FROM `Events` WHERE date('2010-11-08') BETWEEN start AND end";

[DavidAM]

i'm sure BETWEEN would work. i don't know the correct syntax to use though. I'm now familiar with querying with dates.

 

i have something like the following but its not workin...

$date = "2010-11-25";

$query = "SELECT * FROM `Events` WHERE date('2010-11-08') BETWEEN start AND end";

 

That looks good (if start and end are column names in your table and are defined as DATE or DATETIME). Note that the $date (PHP) variable is not being used and is not the same value as you put in the query.

 

You could add code to print mysql_error() if the query fails so you can see why

 

$date = "2010-11-25";
$query = "SELECT * FROM `Events` WHERE date('$date') BETWEEN start AND end";
$result = mysql_query($query);
if ($result === false) {
  printf("Query Failed:\n%s\nError: %s\n", $query, mysql_error());
} else {
  // Looks Good, move on

 

 

[dadamssg]

yes they are fields in my database that are datetimes. The query won't return events that start and end on the same day though, which is what i also want. So this query isn't what i'm looking for...

 

$date = "'2010-11-03 00:00:00";

$query = "SELECT * FROM Events WHERE DATE('$date') BETWEEN start AND end ORDER BY start ASC";

[dadamssg]

i need the correct syntax for this...

 

SELECT * FROM Events WHERE date('$date') BETWEEN start AND end

OR

WHERE date('$date') = date('start')

OR

WHERE date('$date') = date('end')

ORDER BY start ASC

[PFMaBiSmAd]

BETWEEN is inclusive.

 

Is there some reason you have the time included as part of the start/end values?

 

Assuming the $date value is just a date -

 

SELECT * FROM Events WHERE '$date' BETWEEN date(start) AND date(end)

 

^^^ If that doesn't work, I recommend that you post a sample of your data, a $date value, and what the expected results should be.

[dadamssg]

that worked. I wasn't grabbing just the dates of the start and end times. thanks!