S.S S.S -4 years ago 126
MySQL Question

SQL - selecting rows with date and time conditions

I am trying to select all of the rows where it is less than or equal to today's date (This part is working) but i dont want the rows for today where the $time is less than the endt time.

test table: {ID, date, status, checkid}

check table: {checkid, startt, endt}: start and end are time values like below.

$time = '15:00:00';

$query = ("SELECT test.ID, test.status, check.startt, check.endt FROM test INNER JOIN check ON test.checkid=check.checkid
WHERE test.date <='$date' AND $time < check.endt");


The query wont display anything if the time is less than the end time in the check table. I know am doing something wrong but i cant figure it out, i am trying an if statement atm.

Answer Source

Basically code what you described... I.e. Special handling when test.date = "today"

Condition "1" on date only always applies (but excludes today)
Condition "2" involves both date and time (for today).

WHERE    test.date < $date
      OR (test.date = $date AND check.endt <= $time)

NB: Note that if you try combine the date and time so you can perform a single comparison: this is possible; but the query would be unable to leverage indexes, so is not advisable.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download