Mysql Cast as 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).

7

1 Answer

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:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Maya Lin-Takahashi

Maya Lin-Takahashi

Consumer Tech & Gadget Reviewer

Maya is a hardware enthusiast who tests and reviews smart home devices, smartphones, wearables, and audio gear. She focuses on practical consumer value and build quality.

Share this article
Twitter Facebook Pinterest