1

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.

4

You can use the NOW() function in . 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;
  • Actually I had a typo in my question which you propagated. It should be Where appearanceDate < NOW() ... my fault! – Robusto Dec 1 '16 at 21:35
  • I have some date in $today variable and this works for me . $this->db->where(" G.start_date <= '$today' AND G.end_date >= '$today' ",NULL,FALSE); – Sapna Bhayal Jan 9 at 6:33
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.

-3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.