I want to pass Nodejs variable cur_wtr_mk_val
(which is storing a timestamp value) to input parameter in my SQL Server stored procedure which is of datetime
data type. But when I am using the code shown here, I get the following error:
System.Private.CoreLib: Exception while executing function: Functions.d-we2bidl-func03. System.Private.CoreLib: Result: Failure Exception: Error converting data type nvarchar to datetime.
Stack: 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).
My code looks like this:
module.exports = async function (context, req) {
//Authenticating 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)
})
//Fetching Current Water mark value from ADX table
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; // I need to pass this variable storing
// timestamp value 2022-07-16T13:37:36.212Z to the sql stored procedure datetime input
// parameter
//Calling the stored procedure
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);
}
How to fix this error?