CODEWITHSUNDEEP
mysqlsqldatabase
iamtheDanger
iamtheDanger

Reputation: 35

How to obtain data between two dates where end date is Null?

I have a table with date created and end date column. All the elements have a date created value but not necessarily an end date value which can be null. I want to obtain rows by querying between these dates. How should I write the query such that the rows between the date created and end date are displayed along with the data whose end date is null?

Upvotes: 1

Views: 2689

Answers (2)

spencer7593
spencer7593

Reputation: 108410

One option is to use a boolean OR operator.

It's not clear exactly what date(s) values you are supplying in the predicates. This example uses DATE(NOW()) to return the current date.

SELECT ...
  FROM mytable t
 WHERE t.date_created <= DATE(NOW())
   AND ( t.end_date   >= DATE(NOW()) OR t.end_date IS NULL )

There are other query patterns that will return an equivalent result, but they are all going to boil down to the same thing. (Another approach that's often used is to substitute a value for the NULL value. As an example, if end_date is NULL, we use a known value that will result in the boolean TRUE that we want:

SELECT ...
  FROM mytable t
 WHERE t.date_created <= DATE(NOW())
   AND IFNULL(t.end_date,DATE(NOW())) >= DATE(NOW())

Upvotes: 1

Barmar
Barmar

Reputation: 781098

Use this:

WHERE IF(end_date IS NULL, @date > date_created, @date BETWEEN date_created AND end_date)

Upvotes: 1

Related Questions