CODEWITHSUNDEEP
phpmysqlcomparison
thijsdemaa
thijsdemaa

Reputation: 324

Comparing dates mysql php

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 :)

Upvotes: 0

Views: 117

Answers (3)

joaofgf
joaofgf

Reputation: 338

change query to startdate >= instead of <=

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

Upvotes: 0

Hackerman
Hackerman

Reputation: 12305

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;

Upvotes: 0

juergen d
juergen d

Reputation: 204746

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

Upvotes: 1

Related Questions