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
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.