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
--                                                              ^        ^
-- -------------------------------------------------------------+        |
-- ----------------------------------------------------------------------+

Answer 2

The date returned after Substring_Index() operations also contains double quotes. I added double quotes in the format specified for the Str_to_Date() function:

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

Result

| created_date        |
| ------------------- |
| 2018-01-17 15:51:23 |

View on DB Fiddle

---

However, in version 5.7 and above, it would be better to utilize JSON functions instead. We can use Json_Extract() function to get the value corresponding to created_date key, and the use the Str_To_Date() function.

Query #2

SET @j := '[{"key":"jkajdajsdiajsdik"},{"created_date":"2018-01-17 15:51:23"}]';

SELECT STR_TO_DATE(JSON_EXTRACT(@j, '$[*].created_date'), '["%Y-%m-%d %H:%i:%s"]') AS created_date;

Result

| created_date        |
| ------------------- |
| 2018-01-17 15:51:23 |

View on DB Fiddle