MySQL CASE statements using dates

Question

I am trying to use the CASE statement in SQL Query that I have, and it does not work the way I thought it would.

Basically, I have three scripts that I need to execute, and it uses 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 output:

 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, this pulls it 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 easy! Can anyone help?

Answer 1

Try this option -

 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 stream functions in MySQL, you must use one with conditions.