CODEWITHSUNDEEP
mysqlif-statementwhere-clause
Fløtti
Fløtti

Reputation: 53

Using if statement in where clause

With if in a where statement I'm trying to get all rows where datetime is larger then NOW(), but if type = 'economy', i would like to constraint it to only show if datediff between datetime and NOW() is larger than 14 days.

Something like:

Select type,date(datetime) as date
from tbl1
where datetime > NOW()
IF(type = 'economy') && datediff(datetime,NOW()) > 14
order by datetime desc

I've tried with case when also, but can't figure it out...

Upvotes: 2

Views: 6540

Answers (3)

judda
judda

Reputation: 3972

SELECT type, date(datetime) AS date 
FROM tbl1 
WHERE ( type <> 'economy' AND datetime > NOW() ) 
    OR ( type = 'economy' AND datediff(datetime, NOW()) > 14 )

Upvotes: 2

Petar Minchev
Petar Minchev

Reputation: 47373

WHERE ((type <> 'economy') AND (datetime > now)) OR ((type = 'economy') AND (datediff(datetime,NOW()) > 14)

'

Upvotes: 2

Jaymz
Jaymz

Reputation: 6348

You can just separate the criteria into 2 different comparisons:

select type,date(datetime) as date 
from tbl1 
where (datetime > NOW() AND type <> 'economy') OR
      (datediff(datetime, NOW()) > 14 AND type = 'economy')
order by datetime desc

(Not checked for syntax errors, but the idea should be right...)

Upvotes: 10

Related Questions