Converting string extracted from json value to date returning NULL

Question

I'm saving a date in one db field in json format. The json value looks like:

[{"key":"jkajdajsdiajsdik"},{"created_date":"2018-01-17 15:51:23"}]

I want to extract the created_date from json through sql and i'm getting it through the following query.

select SUBSTRING_INDEX(SUBSTRING_INDEX(testjson, 'created_date\":', -1),'}',1) as created_date from test 

The above query returns

"2018-01-17 15:51:23"

Now I'm trying to convert this returned string to a date.

select STR_TO_DATE(SUBSTRING_INDEX(SUBSTRING_INDEX(testjson, 'created_date\":', -1),'}',1),'%Y-%m-%d %H:%i:%s') as created_date from test 

But this is returning NULL. When I tried with a sample

SELECT STR_TO_DATE(" 2018-01-17 15:51:23",'%Y-%m-%d %H:%i:%s')

Its returning

2018-01-17 15:51:23

What am I doing wrong? Can anyone help me to fix this?

Thanks in advance

Answer 1

Your code is also including ". Make this small change to your code to exclude them:

select SUBSTRING_INDEX(SUBSTRING_INDEX(testjson, 'created_date":"', -1),'"}',1) as created_date from test
--                                                              ^        ^
-- -------------------------------------------------------------+        |
-- ----------------------------------------------------------------------+