Grafana对接Synapse数据源时DATEDIFF函数报错排查

错误提示mssql: Conversion failed when converting date and/or time from character string的核心原因是Grafana连接数据库时的会话日期格式/语言设置，与你直接在Azure Synapse查询窗口的设置不一致，导致日期字符串转换失败。DATEDIFF函数本身在Grafana和Synapse中的行为没有差异。

排查与修复步骤 #

1. 明确字段类型，显式指定转换格式 #

如果Betriebstag和prediction_written是字符串类型（而非datetime/date原生类型），不要依赖默认会话的日期解析规则，改用CONVERT函数并指定明确的格式码，确保转换行为一致：

• 若字段是yyyy-MM-dd格式，用格式码23；
• 若带时间（yyyy-MM-dd HH:mm:ss），用格式码120；
• 格式码34对应dd.MM.yy HH:mm，需确保源字符串匹配该格式。

修改后的查询示例：

SELECT TOP (15)
  CONVERT(DATETIME, "Betriebstag", 23) AS Betriebstag_DT,
  CONVERT(VARCHAR, CONVERT(DATETIME, "Betriebstag", 23), 34) AS "betriebstag",
  "MWNR",
  "kwhUsed" AS "Ist-Verbrauch",
  "kWh_prediction" AS "ML Prognose",
  "kwh_prediction_linear_temperature" AS "Lineare Prognose",
  CONVERT(DATETIME, "prediction_written", 23) AS prediction_written_DT
FROM [dbo].[XYZ]
WHERE CONVERT(DATETIME, "Betriebstag", 23) BETWEEN CONVERT(DATETIME, '2023-08-01', 23) AND CONVERT(DATETIME, '2023-08-15', 23)
  AND "MWNR" = 4021
  AND DATEDIFF(day, CONVERT(DATETIME, "Betriebstag", 23), CONVERT(DATETIME, "prediction_written", 23)) = -1
  AND kwhUsed IS NOT NULL
ORDER BY CONVERT(DATETIME, "Betriebstag", 23) ASC

2. 统一Grafana会话的日期设置 #

如果不想修改查询，可以在Grafana的MSSQL数据源配置中，添加额外连接参数强制会话使用ISO日期格式：

• 打开Grafana数据源设置，找到Additional connection parameters；
• 输入DATEFORMAT=ymd，保存后重新执行查询。

3. 检查字段值的合法性 #

确保Betriebstag和prediction_written字段中没有无效的日期字符串（比如格式不统一、非法日期值）——Synapse查询窗口可能自动忽略部分错误，而Grafana的数据源驱动对转换错误更敏感。

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