How to select records where NOW is between two dates in fields?

Question

I've searched all over for this, and I seem only to find how to get records where a single date is between two "outside" dates.

I want to find out how to select records where the current date is between the value in the startDate field and the value in the endDate field.

What I have so far (PHP):

$now = new DateTime();
$sql = "
    SELECT content, image, imageHeight, imageWidth, link, linkDescription 
    FROM news 
    WHERE appearanceDate < $now  
      AND $now < termDate 
    ORDER BY appearanceDate DESC
";

But it doesn't work.

Answer 1

You can use the NOW() function in mysql. Note, this assumes that termDate is the later date:

Select content, image, imageHeight, imageWidth, link, linkDescription
  From news
  Where appearanceDate < NOW() 
    And termDate > NOW()
  Order By appearanceDate Desc;

Answer 2

If you want an inclusive range you can use the slightly nicer syntax afforded by BETWEEN

WHERE NOW() BETWEEN appearanceDate AND termDate

If expr is greater than or equal to min and expr is less than or equal to max, BETWEEN returns 1, otherwise it returns 0. This is equivalent to the expression (min <= expr AND expr <= max) if all the arguments are of the same type. Otherwise type conversion takes place according to the rules described in Section 13.2, “Type Conversion in Expression Evaluation”, but applied to all the three arguments.

Answer 3

You could use the MySQL NOW() function to get the current timestamp.