MySQL CASE statements using dates

Question

I'm trying to use a CASE statement in a SQL query I have and it's not working the way I thought it would do.

Basically, I have three scenarios I need to fulfil and it using a date field so for example I have the following data:

id | date_activated
1  | 2011-10-10 07:00:06
2  | 2011-03-12 10:00:00
3  | 2011-11-27 18:10:36
4  | 2010-01-25 14:30:43
5  | 0000-00-00 00:00:00

using the following SQL:

select id,
case date_activated
when date_activated > '2011-11-23 18:30:00' then 'after'
when date_activated > '2010-01-20 00:00:00' then 'before'
else 'not yet'
end as date_note
from table1

should bring out:

id | date_activated       | date_note
1  | 2011-10-10 07:00:06  | before
2  | 2011-03-12 10:00:00  | before
3  | 2011-11-27 18:10:36  | after
4  | 2010-01-25 14:30:43  | before
5  | 0000-00-00 00:00:00  | not yet

However, it's pulling this out:

id | date_activated       | date_note
1  | 2011-10-10 07:00:06  | not yet
2  | 2011-03-12 10:00:00  | not yet
3  | 2011-11-27 18:10:36  | not yet
4  | 2010-01-25 14:30:43  | not yet
5  | 0000-00-00 00:00:00  | after

I can't understand what I'm doing wrong but I bet it's something simple!

Answer 1

Try this one -

SELECT
  id,
  CASE
    WHEN date_activated > '2011-11-23 18:30:00' THEN 'after'
    WHEN date_activated > '2010-01-20 00:00:00' THEN 'before'
    ELSE 'not yet'
  END AS date_note
FROM table1;

There are two CASE flow functions in MySQL, you should use one with conditions.