In the MySQL database I have a start date and end date specifed, which are in the format yyyy-mm-dd, and I want to these dates to filter records. IE, start date should be less than current date and finish date should be more than current date. I have tried the syntax

SELECT *
FROM products
WHERE SpecialGoLiveDate <= '# getDate() #' AND SpecialEndDate >= '# getDate() #'
ORDER BY RAND() LIMIT 4

The problem I am having is that it doesn't seem to be comparing the date fields to todays date, or if it is, it is comparing it as a number and not a date.

Please help me!!

One problem is the #'s. Thats an MS Access thing.

try:

$datetime = date("Y-m-d H:i:s";
$query = ('SELECT * FROM products WHERE SpecialGoLiveDate <= ' . $datetime . ' AND SpecialEndDate >= ' . $datetime . ' ORDER BY RAND() LIMIT 4')

Thanks very much for your reply.

I've found a solution thanks to Peter Ferrera.

I've replaced where I had <= '# date() #' with <=NOW()

It seems to work ok, so entire syntax is now:

$query = "SELECT * FROM News
WHERE StartDate <=NOW()
AND EndDate >= NOW()