Between Dates

meHere

I have a start date and an end date for each event in my DB and
I then have a day date which is entered

and I am trying to find all events which will be occuring on the entered day date,

How do I find the dates of the dates between the start date and the end date to compare them to the entered date? or would I be better to just store the start date and then the duration of the event?
I have trying using the between statement and the < > operators but it doesn't work properly has anyone any ideas?

Any help will be grately appreciated Thanks.

LordShryku

SELECT *
FROM table
WHERE date_field
BETWEEN 'start_date'
AND 'end_date'

This will work providing the fields are actual date-type fields(date, datetime, etc)

sfullman

I assume someone is entering a date from a form.

You must first convert that date into whatever format you have your EventStart and EventFinish fields, e.g. suppose it's a full datetime:

$date = date('Y-m-d H:i:s', $_POST[theirDate]);
if(!date){
    #invalid date -- return to the form with err message
}esle{
   $sql="SELECT * from yourtable WHERE EventStart <= '$date' AND EventFinish >= '$date' ";
}

This skips using the mySQL date functions entirely, be aware that this is a pure numeric comparison in that case, just because the string lengths are the same.

And you have to validate the user's input so you may as well convert it.

meHere

Thanks for your suggestions but they don't work,

they don't work when say an event
starts on 2004-05-05 and ends on 2004-05-08

and say I enter the date 2004-05-06 to view all events occuring on this date

I need the above event to be displayed and I have no idea how to do it!

sfullman

You must have an error somewhere. Look, try this without even referencing a field in your database:

SELECT '2004-05-05' < '2004-05-05' -- returns 0
SELECT '2004-05-05' < '2004-05-06' -- returns 1

You CAN definitely compare strings that way.

just make sure the format is the same, the hierarchy is the same (i.e. don't compare YYYY-MM-DD with YYYY-DD-MM for example).

meHere

Sorry you where right all along,

the problem was I had the start and end date stored as YYYY-MM-DD but the date entered from the form stored as YYYYMMDD without the '-' character

Thanks very much for all your help

Now I have another script written to find all events that occur between two dates,
the first date is entered and then the nxt number of days and the end date is calculated from this.

I can find all events which begins between these two dates by using:

$dbQuery = "SELECT * FROM Events WHERE EventDate between '$startDate' and '$endDate' ";

but how can I find events which may not begin but are occuring between these two dates I tried using

$dbQuery = "SELECT * FROM Events WHERE EventDate between '$startDate' and '$endDate' or".
"EventDate <= '$startDate' AND EventEndDate >='$endDate'";

but it doesn't work have you any ideas?

Thank-you