Comparing dates mysql php

Question

I am trying to make a calendar view. I need to check if a $currentDateTime is between a startdate and enddate in a mysql query.

I have tried this query, based on Compare dates in MySQL

SELECT id, startdate, enddate FROM hirings
WHERE startdate <= '$currentDateTime'
AND enddate <= '$currentDateTime'

where $currentDateTime is in format dd-mm-yyyy hh:mm

but it doesn't seem to work, I keep getting 0 rows returned.

Any help would be appreciated :)

Answer 1

Make sure you use the default datetime format YYYY-MM-DD HH:mm:ss

SELECT id, startdate, enddate
FROM hirings
WHERE '$currentDateTime' between startdate AND enddate

or if you want to use the current SQL time then you don't even need a parameter to your query

SELECT id, startdate, enddate
FROM hirings
WHERE NOW() between startdate AND enddate

Answer 2

change query to startdate >= instead of <=

SELECT id, startdate, enddate FROM hirings WHERE startdate >= '$currentDateTime' AND enddate <= '$currentDateTime'

Answer 3

Try this:

SELECT hr.id, hr.startdate, hr.enddate 
FROM hirings hr
WHERE STR_TO_DATE('$currentDateTime', '%d-%m-%Y %H:%i:%s')
between hr.startdate AND hr.enddate;