如何修复Node.js向SQL Server存储过程传datetime参数的类型转换错误

尝试将Node.js中存储时间戳的变量cur_wtr_mk_val传递给SQL Server存储过程的datetime类型输入参数时，出现以下错误：

System.Private.CoreLib: 执行函数Functions.d-we2bidl-func03时发生异常。System.Private.CoreLib: 结果：失败
异常：数据类型nvarchar转换为datetime失败。

堆栈：RequestError: Error converting data type nvarchar to datetime。

at handleError (C:\Users\INPTV\Desktop\deepsea_function\node_modules\mssql\lib\tedious\request.js:384:15)

at Connection.emit (node:events:527:28)

at Connection.emit (C:\Users\INPTV\Desktop\deepsea_function\node_modules\tedious\lib\connection.js:1079:18)

at RequestTokenHandler.onErrorMessage (C:\Users\INPTV\Desktop\deepsea_function\node_modules\tedious\lib\token\handler.js:365:21)
at Readable. (C:\Users\INPTV\Desktop\deepsea_function\node_modules\tedious\lib\token\token-stream-parser.js:26:33)
at Readable.emit (node:events:527:28)
at addChunk (node:internal/streams/readable:315:12)
at readableAddChunk (node:internal/streams/readable:289:9)
at Readable.push (node:internal/streams/readable:228:10)
at next (node:internal/streams/from:98:31)。

相关代码如下：

module.exports = async function (context, req) {

 // 认证SQL Server
   const sql = require('mssql')
   const sqlConfig = {
   server: "",
   port: ,
   user: "",
   password: "",
   database: "",
   options: {
   enableArithAbort:true,
   encrypt: true, // for azure
  trustServerCertificate: false // change to true for local dev / self-signed certs
},
  pool: {
  max: 10,
  min: 0,
  idleTimeoutMillis: 30000
  }
};

sql.on('error', err=> {
  console.log(err.message)
})

// 从ADX表获取当前水位标记值
const results = await client.execute("VesselTelemetry","VesselTelemetry_DS_Data | extend 
  Enqueued_time = todatetime(ingested_timestamp) | summarize max_Enqueued_time =  
  max(Enqueued_time)");
  const obj1 = results.primaryResults[0].toString();
  const obj2 = JSON.parse(obj1);
  var cur_wtr_mk_val = obj2.data[0].max_Enqueued_time; // 需将存储时间戳值2022-07-16T13:37:36.212Z的该变量传入SQL Server存储过程的datetime输入参数


 // 调用存储过程
 let sql_config = await sql.connect(sqlConfig)
 let sp_query = await sql_config.request().query(`exec 
 WW_DL_MIG_CFG.SP_DB_INC_MIG_DL_PIP_AUD_DELTA_INF @LastModifiedtime = "+cur_wtr_mk_val+"  `)
 const sp_stringify = JSON.stringify(sp_query);
 const sp_parse = JSON.parse(sp_stringify);
 console.log(sp_parse);
 }

1. 使用参数化查询替代字符串拼接 #

直接拼接字符串会把时间戳以nvarchar类型传递，导致SQL Server无法正确转换为datetime。改用mssql库的input方法明确指定参数类型：

修改存储过程调用代码：

// 调用存储过程
let sql_config = await sql.connect(sqlConfig);
// 创建请求对象并添加参数
let request = sql_config.request();
// 明确指定参数类型为datetime（根据存储过程参数类型调整）
request.input('LastModifiedtime', sql.DateTime, cur_wtr_mk_val);
// 执行存储过程
let sp_query = await request.execute('WW_DL_MIG_CFG.SP_DB_INC_MIG_DL_PIP_AUD_DELTA_INF');
const sp_stringify = JSON.stringify(sp_query);
const sp_parse = JSON.parse(sp_stringify);
console.log(sp_parse);

2. 确保时间变量为合法的Date对象 #

如果cur_wtr_mk_val是字符串格式（如2022-07-16T13:37:36.212Z），先转换为Date对象，让mssql库自动处理类型转换：

var cur_wtr_mk_val = new Date(obj2.data[0].max_Enqueued_time);

3. 匹配存储过程的参数类型 #

检查存储过程SP_DB_INC_MIG_DL_PIP_AUD_DELTA_INF中@LastModifiedtime的实际类型：

• 如果是datetime2，代码中指定sql.DateTime2：

  request.input('LastModifiedtime', sql.DateTime2, cur_wtr_mk_val);
  

• 如果是smalldatetime，则使用sql.SmallDateTime。

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