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

Looks like you've got a number of seconds there. Relative to 1970? `dateadd(second, X, '19700101')` — shawnt00, Sep 10 '20 at 16:07
It's a UnixTime including milliseconds, thus you have to divide by 1000. But you better convert the static timestamp to UnixTime * 1000. Both converting to and from UnixTime can be done in every DBMS, you have to look it up. — dnoeth, Sep 10 '20 at 16:21
What version will matter a LOT here. Sql Server 2019 adds some native JSON support, and while this still naturally breaks any index you might want to use on the column, it still makes things a little nicer. But the best thing you could do is understanding what JSON you'll store and design the schema to support those fields, or at least some of the basic metadata. Then you get regular SQL syntax, as well huge performance gains because indexes will actually work. — Joel Coehoorn, Sep 10 '20 at 17:25

Barbaros Özhan · Answer 1 · 2020-09-10T16:38:58.623

0

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

edited Sep 10 '20 at 16:38

answered Sep 10 '20 at 16:28

Barbaros Özhan

59,113
10
31
55

It probably isn't safe to assume all these dates are 13 characters. – shawnt00 Sep 10 '20 at 16:30

Joel Coehoorn · Answer 2 · 2020-09-10T17:43:15.707

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.

score 0 · Accepted Answer · answered Sep 10 '20 at 19:35

0

I solved the problem using

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

answered Sep 10 '20 at 19:35

Raouf Ghrissi

183
2
9

Compare Date saved as varchar with DateTime

3 Answers3