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.
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)