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

mysql

Solution

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.

Problem

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?

Original source