CODEWITHSUNDEEP
mysqlsqlnull
aleafonso
aleafonso

Reputation: 2256

MySQL query. Can I add a conditional in the WHERE clause?

Basically, I am doing something like this:

select a.name from schedule as a
where date(now()) <= a.dateTo;

But a.dateTo can be null. So, I would like a query to do something like this:

select a.name from schedule as a
where date(now()) <= a.dateTo IF a.dateTo is not null;

That's just a simplified way to explain what I am trying to achieve.

Any ideas?

UPDATE:

The following example might give a better idea of what is required

select a.name from schedule as a
where date(now()) >= a.dateFrom and 
      date(now()) <= a.dateTo;

If I add the OR a.dateTo is null, I think it will override the first condition: date(now()) >= a.dateFrom, which is not the desired query. Something similar would happen with the use of AND.

If a.dateTo is null, it will be > date(now()), so those records should be retrieved, if the first condition also applies: date(now()) >= a.dateFrom

Thanks for your help

Upvotes: 0

Views: 576

Answers (5)

nikc.org
nikc.org

Reputation: 16952

I'm not entirely sure I understood how you want NULL values to behave, but perhaps this would work for you:

SELECT a.name 
FROM schedule AS a
WHERE DATE(NOW()) >= a.dateFrom 
AND DATE(NOW()) <= COALESCE(a.dateTo, NOW());

But this is really the same as saying

SELECT a.name 
FROM schedule AS a
WHERE DATE(NOW()) >= a.dateFrom
AND (a.dateTo IS NULL OR DATE(NOW()) < a.dateTo)

Not sure which is more efficient.

Upvotes: 0

Adrian Brown
Adrian Brown

Reputation: 466

Ive not tested it but something like

SELECT a.name FROM schedule as a WHERE (a.dateTo IS NOT NULL AND date(now()) <= a.dateTo)

should work

Upvotes: 0

Radu Murzea
Radu Murzea

Reputation: 10900

How about:

SELECT a.name FROM schedule AS a WHERE date(NOW()) <= a.dateTo AND a.dateTo IS NOT NULL

I think this will work.

Upvotes: 0

Eugen Rieck
Eugen Rieck

Reputation: 65274

select a.name from schedule as a where curdate() <= ifnull(a.dateTo,curdate());

Upvotes: 1

araqnid
araqnid

Reputation: 133482

You can use and and or logical operators:

where date(now()) <= a.dateTo
      OR a.dateTo is null

Upvotes: 0

Related Questions