Search code examples
sqlsql-serverdatetimejson-value

Compare Date saved as varchar with DateTime

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

Solution

  • I solved the problem using

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