mysql cast date,MySQL CAST为DATE

I'm trying to understand what casting a value to the DATE type in MySQL does. Here are some things I have tried:

SELECT CAST('3' AS DATE);

-- null

SELECT CAST(3 AS DATE);

-- null

SELECT CAST('2014-07-01 19:00:01' AS DATE);

-- 2014-07-01

SELECT DATE('2014-07-01 19:00:01');

-- 2014-07-01

SELECT CAST('2014-07-01' AS DATE);

-- 2014-07-01

SELECT DATE('2014-07-01');

-- 2014-07-01

SELECT CAST('2014-07-50' AS DATE);

-- null

SELECT DATE('2014-07-50');

-- null

SELECT DATE(''), CAST('' AS DATE), DATE(0), CAST(0 AS DATE);

-- null, null, 0000-00-00, 0000-00-00

Everything I've tried seems to either convert it to null if it's invalid or return the date portion of the string if it's a valid date. I've even tried dates with slashes and other formats, same results.

What's the difference between using the DATE(expr) function and the CAST(expr AS DATE)?

DATE(expr): Extracts the date part of the date or datetime expression expr.

CAST(expr AS type): The CAST() function takes an expression of any type and produces a result value of a specified type, similar to CONVERT()

Similarly, the same question can be asked about times with TIME(expr) and CAST(expr AS TIME).

解决方案

Checking the source code of MySQL 5.6 CAST() and CONVERT() calls to the same internal function Item_date_typecast, DATE() calls to Item_date_typecast too.

In conclusion there is no difference between DATE(expr) function and the CAST(expr AS DATE).

Refs: