I have a table with JSON data in one of the columns and i'm trying to parse the JSON data and insert into a temp table
DECLARE @TEMPTABLE
(
ID INT,
Status NVARCHAR(50),
Cost DECIMAL(20, 0)
)
INSERT INTO @TEMPTABLE
SELECT
ID,
JSON_VALUE(mydata, '$.Status') AS Status,
JSON_VALUE(mydata, '$.Cost') AS Cost
FROM Expense
I get this error:
Error Converting data type nvarchar to numeric
The same works fine if I comment out the Cost
column.
Sample JSON data in Cost table
| ID | mydata
+----+-------------------------------------
| 1 | {"Status":"Shipped","Cost":"$10.50"}
You can convert the value to MONEY. It is a little more forgiving than decimal()
Example
Returns