CODEWITHSUNDEEP
mysql
noober
noober

Reputation: 1505

using mysql datediff with an elseif or case statement

I have the following line of code that can either return one of 2 conditions ('Expired','Active'). I needed to use DATEDIFF to figure out the dates I needed.

However, now I need to alter this code so that I can add a sort of else if condition if neither of the 2 conditions are met and result to 'Unknown'.

I have this so far:

SELECT        
IF(DATEDIFF(@endDate:=ADDDATE(h.StartDate,Interval h.NumMonth Month),NOW())<0,'Expired', 
        IF(DATEDIFF(@endDate,NOW())<120,'Expires in 120 days or less','Active')) AS bActive 
FROM ...

So, for now, I have Expired, Active but I also want to include else 'Unknown' to be included as option. How would I alter this logic? Do I need to use ELSEIF or CASE? What should I use?

Upvotes: 0

Views: 454

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270523

Use case instead of if. First, case is ANSI standard, so it works across databases. Second, it handles multiple conditions better. I think your logic is:

SELECT (CASE WHEN DATEDIFF(ADDDATE(h.StartDate, Interval h.NumMonth Month), NOW()) < 0
             THEN 'Expired'
             WHEN DATEDIFF(ADDDATE(h.StartDate, Interval h.NumMonth Month), NOW()) < 120
             THEN 'Expires in 120 days or less'
             ELSE 'Active'
        END) AS bActive

Upvotes: 1

Related Questions