Why is CASTing VarChar to DateTime resulting in null?

Question

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.

This is your answer: https://stackoverflow.com/questions/5201383/how-to-convert-a-string-to-date-in-mysql — CodeLikeBeaker, Dec 11 '17 at 22:10
`STR_TO_DATE(review_date, '%m/%d/%Y')` Also just creates a collum of `NULL` values. My question is more centered around why `CAST` and `CONVERT` aren't working. — hackerman, Dec 11 '17 at 22:14

score 3 · Accepted Answer · answered Dec 11 '17 at 22:28

3

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 Dec 11 '17 at 22:28

CodeLikeBeaker

20,682
14
79
108

1

Okay, that example helps explain things, I didn't realize the yyyy-mm-dd was essential to cast or convert to datetime – hackerman Dec 11 '17 at 22:39
No problem, glad I could help – CodeLikeBeaker Dec 11 '17 at 22:42

score -1 · Answer 2 · edited Mar 24 '22 at 17:30

-1

This is for MYSQL (where I converted my string to date function)

SELECT cast(str_to_date(column_name), '%m/%d/%y') as date) 
FROM Table Name;

Example:

SELECT cast(str_to_date(Release_Date, "%m/%d/%y") as date) 
FROM movie-data;

Make sure that you use the delimiter in your date format correctly. Like in my case the date was in the string column was separated by '/'. In your case it might be separated with a comma ',' or dot '.'. Make sure you use that.

Example: 3/8/2016  "%m/%d/%y"
         3,8,2016  "%m,%d,%y"
         3.8.2016  "%m.%d.%y"

edited Mar 24 '22 at 17:30

PM 77-1

12,933
21
68
111

answered Mar 22 '22 at 13:22

uma negi

1

Please explain how this is related to the question instead of just providing queries. Try to indent your code to have proper formatting https://stackoverflow.com/editing-help – Almaju Mar 22 '22 at 20:07

Why is CASTing VarChar to DateTime resulting in null?

2 Answers2