How to search in a string for the date between dateFrom and dateTo

Question

I have three parameters, $motivazione, $dateFrom and $dateTo, in my table I have the following string: {"motivation": 17, "dfp": "31/10/2022"}

The query must return the id of the row if the string in the db contains motivazione: $motivazione and dfp: between dateFrom and dateTo,

with the following query I can only get the date contained in the string:

SELECT column, SUBSTRING (column, CHARINDEX ('dfp', column) +6, 10) FROM myTable

but I can't compare it

how can I do?

Thank you

Answer 1

Convert your date string to a SQL Server date: CONVERT(DATE,'31/10/2022', 103).

The format 103 is dd/mm/yyyy. If you don't specify it, the date might be interpreted in the US format mm/dd/yyyy.

Also, if you have a JSON string, use @AnthonyG's advice to extract the date value.

A where clause would be something like this

WHERE CONVERT(JSON_VALUE(Data, '$.dfp'), 103) BETWEEN '2022-01-01' AND '2022-01-31'

Use the year first format for date literals, then you will not have the dd/mm - mm/dd issue.