Comparing datetime

[kevnin]

I've got a table with two datetime fields (startDate and endDate).

 

I want to select rows where today's date lies between startDate and endDate.

 

I have this currently:

SELECT p.positionID, p.positionName, s.name, p.description, p.startDate, p.endDate 
FROM position as p INNER JOIN service as s ON p.serviceID=s.serviceID 
WHERE p.endDate < 2010-06-01 05:06:06 AND p.startDate < 2010-06-01 05:06:06 
ORDER BY s.name, p.positionName

That date/time is created with PHP date().

Error:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '05:06:06 AND p.startDate < 2010-06-01 05:06:06 ORDER BY s.name, p.positionName ' at line 3

 

If I change the WHERE to something trivial, it works fine.

Searching hasn't gotten me very far sadly, so I come to you guys for help.

[dabaR]

Hi there. In general you want to post the code that is generating undesired output. Then I could modify it to work exactly.

 

Do you want >= startdate and <= enddate? That would include the dates on which something starts and ends. Otherwise you get just the dates in between. The error is likely because you need to have '2010-06-01' within quotes like that, and you can get that with something like "'" . date('Y-m-d') . "'"

[kevnin]

Ah, yes, it was because of the quotes.  Can't believe I missed that.  I guess that's what 12+ hours of coding in a day will do to you.

 

Also, yes, I had the comparison wrong. I did want

startDate <= '$date' AND endDate >= '$date'

 

Thanks!