SQL Server中JSON列提取transactionTimeStamp与时间戳列比较报错问题

SQL Server不支持JSON_EXTRACT函数，这个函数是MySQL等其他数据库的语法，在SQL Server里提取JSON字段值要改用JSON_VALUE函数。

基础筛选写法 #

假设你的表名为YourTable，Config_MSG的JSON结构类似{"message": {"transactionTimeStamp": "2024-05-20 14:30:00"}}，可以用以下SQL语句筛选符合条件的记录：

SELECT Id, Config_MSG, CreatedDate_TimeStamp
FROM YourTable
WHERE CONVERT(DATETIME, JSON_VALUE(Config_MSG, '$.message.transactionTimeStamp')) > CreatedDate_TimeStamp;

注意事项 #

• 如果transactionTimeStamp的格式不是SQL Server默认的datetime格式，建议用TRY_CONVERT或带格式参数的CONVERT确保转换成功，避免报错：

  -- 处理ISO8601格式的时间字符串
  WHERE TRY_CONVERT(DATETIME2, JSON_VALUE(Config_MSG, '$.message.transactionTimeStamp'), 127) > CreatedDate_TimeStamp;
  

• 若JSON结构嵌套复杂，可使用OPENJSON解析后关联筛选：

  SELECT t.Id, t.Config_MSG, t.CreatedDate_TimeStamp
  FROM YourTable t
  CROSS APPLY OPENJSON(t.Config_MSG, '$.message')
  WITH (transactionTimeStamp DATETIME2 '$.transactionTimeStamp') AS j
  WHERE j.transactionTimeStamp > t.CreatedDate_TimeStamp;
  

内容的提问来源于stack exchange，提问作者Prakash