Getting datetime date and time (error in MySQL?)

Question

By running the following statement, MySQL seems to be mixing things up:

select now(), if(false, date(now()), time(now())); | 2013-07-24 10:06:21 | 2010-06-21 00:00:00 | 

If you replace the second if argument with a literal string, the instruction will behave correctly:

 select now(), if(false, 'Banana', time(now())); | 2013-07-24 10:06:21 | 10:06:21 | 

Is this a mistake or some strange quirk?

Answer 1

The returned IF type must be a data type that includes the types of both arguments. Therefore, if one of the arguments is DATE and the other is TIME , the IF type will be DATETIME .

This does not seem necessary in a trivial request example, but consider something like:

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

All rows of the result must have the same data type in the dt column, although the specific data will depend on what is in that row.

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