Getting date and time of datetime (bug in MySQL?)

Question

Running the following statement, MySQL seems to mix things up:

select now(), if(false, date(now()), time(now()));

| 2013-07-24 10:06:21 | 2010-06-21 00:00:00 |

If replacing the second argument of the if with a literal string, the statement behaves correctly:

select now(), if(false, 'Banana', time(now()));

| 2013-07-24 10:06:21 | 10:06:21 |

Is this a bug or some really strange quirk?

Answer 1

The return type of IF has to be a datatype that includes the types of both arguments. So if one of the arguments is a DATE and the other is a TIME, the type of IF will be DATETIME.

This doesn't seem necessary in the trivial example query, but consider something like:

SELECT IF(col1, date(col2), time(col2)) AS dt
FROM Table

All the rows of the result have to have the same datatype in the dt column, even though the specific data will depend on what's in that row.

If you want just the date or time, convert it to a string.