Chaining JSON_EXTRACT with CAST or STR_TO_DATE fails
<p>I'm trying to extract a datetime from a JSONFIELD "data" in MySQL.</p>

<p>If I do a simple JSON_EXTRACT however, the return field type is a JSON.</p>

<pre><code>mysql&gt; select JSON_EXTRACT(data, "$.new_time") from analytics limit 10;
+----------------------------------+
| JSON_EXTRACT(data, "$.new_time") |
+----------------------------------+
| NULL                             |
| "2016-09-30T04:00:00+00:00"      |
| "2016-09-29T05:30:00+00:00"      |
| NULL                             |
| "2016-10-01T05:30:00+00:00"      |
| "2016-09-27T23:00:00+00:00"      |
| NULL                             |
| "2016-09-23T01:30:00+00:00"      |
| "2016-09-23T04:00:00+00:00"      |
| "2016-09-27T01:30:00+00:00"      |
+----------------------------------+
</code></pre>

<p>I want to convert this to a MySQL DATETIME. But if I chain JSON_EXTRACT and STR_TO_DATETIME, I get all NULL values:</p>

<pre><code>mysql&gt; select STR_TO_DATE(JSON_EXTRACT(data, "$.new_time") ,"%Y-%m-%d") from analytics_calendaranalytics limit 10;
+-----------------------------------------------------------+
| STR_TO_DATE(JSON_EXTRACT(data, "$.new_time") ,"%Y-%m-%d") |
+-----------------------------------------------------------+
| NULL                                                      |
| NULL                                                      |
| NULL                                                      |
| NULL                                                      |
| NULL                                                      |
| NULL                                                      |
| NULL                                                      |
| NULL                                                      |
| NULL                                                      |
| NULL                                                      |
+-----------------------------------------------------------+
</code></pre>

<p>Likewise, a CAST as DATETIME also fails:</p>

<pre><code>mysql&gt; select CAST(JSON_EXTRACT(data, "$.new_time") as DATETIME) from analytics_calendaranalytics limit 10;
+----------------------------------------------------+
| CAST(JSON_EXTRACT(data, "$.new_time") as DATETIME) |
+----------------------------------------------------+
| NULL                                               |
| NULL                                               |
| NULL                                               |
| NULL                                               |
| NULL                                               |
| NULL                                               |
| NULL                                               |
| NULL                                               |
| NULL                                               |
| NULL                                               |
+----------------------------------------------------+
</code></pre>

<p>Both these commands work when I start with the string value:</p>

<pre><code>mysql&gt; select CAST("2016-09-30T04:00:00+00:00" as DATETIME);
+-----------------------------------------------+
| CAST("2016-09-30T04:00:00+00:00" as DATETIME) |
+-----------------------------------------------+
| 2016-09-30 04:00:00                           |
+-----------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql&gt; select STR_TO_DATE("2016-09-30T04:00:00+00:00", "%Y-%m-%d");
+------------------------------------------------------+
| STR_TO_DATE("2016-09-30T04:00:00+00:00", "%Y-%m-%d") |
+------------------------------------------------------+
| 2016-09-30                                           |
+------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)
</code></pre>

<p>Would appreciate any help in resolving this!</p>