Compare Date saved as varchar with DateTime

Question

I have a table with a column jsonStr of type varchar.

This is an example of an element in this column

{"Date":"/Date(1602846000000)/","person":"Laura"}

I want to compare this date with a static date. This is my query:

select * 
from mytable 
where json_value(jsonStr, '$.Date') >= '2020-10-01T00:00:00'

I expected one element to be displayed but no result so how can I convert this date to compare it with DateTime

I tried to remove /Date and / with substring and then Convert / Parse the result which is 1602846000000 but no result

Answer 1

I solved the problem using

DATEADD(SECOND, CONVERT(INT, Left(SUBSTRING(JSON_VALUE(jsonStr, '$.EndDate'), 7, 13), 10)), '19700101'

Answer 2

Extracted unixtime value might be converted to datetime format through use of

DATEADD(S, CONVERT(int,LEFT(1602846000000, 10)), '1970-01-01') such as :

WITH t AS
(
SELECT *, JSON_VALUE(jsonStr, '$.Date') AS str
  FROM mytable 
), t2 AS
(
SELECT t.*, 
       SUBSTRING(str, PATINDEX('%[0-9]%', str), PATINDEX('%[0-9][^0-9]%', str + 't') 
       - PATINDEX('%[0-9]%', str) + 1) AS nr
  FROM t
)
SELECT t2.jsonStr
  FROM t2
 WHERE DATEADD(S, CONVERT(int,LEFT(nr, 10)), '1970-01-01') >= '2020-10-01T00:00:00'

Demo

Answer 3

I would reverse this as much as possible. Every bit of work you do for this comparison must done for every row in your table, because we don't know which rows will match until after we do the work. The more we can do to the constant value, rather than all the stored values, the more efficient the query becomes.

Parsing dates out of JSON is stupid expensive to do in the database. We can't get rid of that work completely, but we can at least convert the initial date string into the unix time format before including in the SQL. So this:

'2020-10-01T00:00:00'

becomes this:

1601510400

Now you can do some simpler string manipulation and compare the numbers, without needing to convert the unix time into a date value for every single row.

What that string manipulation will look like varies greatly depending on what version of Sql Server you have. Sql Server 2019 adds some new native JSON support, which could make this much easier.

But either way, you're still better off taking the time to understand the data you're storing. Even when keeping the raw json makes sense, you should have a schema that at least supports basic metadata on top of it. It's difference between using an index or not, which can make multiple orders magnitude difference for performance.

For example, as previously mentioned the query in this question must extract the date value for every row in your table... even the rows that won't match. If you build a schema where the date was identified as meta and extracted during the initial insert, an index could let you seek to just the rows you need. If at this point you still need to extract a value from JSON records, at least it's just for the relevant rows.