[SOLVED] Why is CASTing VarChar to DateTime resulting in null?

Issue

I’m running MySQL and trying to convert the following row from VarChar to DateTime

+-------------------+
|    review_date    |
+-------------------+
| May 24, 2015      |
| June 3, 2013      |
| December 19, 2003 |
+-------------------+

I have tried CAST(review_date as DATETIME) However that results in a column of null values when I test it in the SELECT statement.

I also tried CONVERT(DATETIME, review_date, 107) however, it resulted in: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'review_date, 107) at line 3

I haven’t converted to date time before but I can’t understand how the above statements deviate from the documentation or why they wouldn’t be working.

I used this syntax to create for the column: review_date varchar(255) DEFAULT NULL. And STR_TO_DATE(review_date, '%m/%d/%Y') also yields a column of Null cells.

Solution

The problem is when you cast or convert, your string value isn’t in the standard yyyy-mm-dd format.

If you run this example, you will see what I mean. This example also has the proper way to convert string to date:

SELECT
    CAST(review_date AS DATETIME) -- gives null due to incorrect format
    , STR_TO_DATE(review_date, '%M %d, %Y') -- gives correct date
    , CAST('2015-05-24' AS DATE) -- gives date due to correct format
FROM
    date_test

Answered By – CodeLikeBeaker

Answer Checked By – Cary Denson (BugsFixing Admin)