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

What is your MySQL server version ? Newer versions have JSON specific functions available. — Madhur Bhaiya, Nov 27 '18 at 12:34

Madhur Bhaiya · Answer 1 · 2018-11-27T12:58:47.173

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

@Jenz happy to help. Check the updated answer; it will be better to use JSON functions, if your MySQL version supports it. — Madhur Bhaiya, Nov 27 '18 at 12:59

score 2 · Accepted Answer · answered Nov 27 '18 at 12:41

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

Converting string extracted from json value to date returning NULL

2 Answers2