Mysql – thesql date between today

betweendateMySQL

Table:

  • Activity: id, name, date, time, endDate, endTime

Basically I got a query that saids:

TODAY:
SELECT * FROM activities WHERE DATE(A.date) = DATE(CURDATE())

TOMORROW:
SELECT * FROM activities WHERE DATE(A.date) = (CURDATE() + INTERVAL 1 DAY)

NEXT WEEK:
SELECT * FROM activities WHERE UNIX_TIMESTAMP(A.DATE) BETWEEN '" . strtotime('previous saturday') . "' AND '" . (strtotime('previous monday') - 1) . "'

The problem is, and I have no idea how to solve it, when the enddate is inserted, it should search between the date and the enddate. So if an activity started yesterday and it will end today, and we select the "today" query, we still want the activity.

Best Answer

SELECT *
FROM activities
WHERE DATE(now()) BETWEEN DATE(A.startdate) AND DATE(B.enddate)

the 'between' syntax isn't limited to just field between val1 and val2, it can also be val between field1 and field2 and other arbitrary expression as well.

Related Topic