How to convert node.js date to SQL Server compatible datetime?

gog picture gog · May 18, 2016 · Viewed 13.7k times · Source

I have a Web Api using express and Tedious to store some data on Azure SQL database. With nvarchar types and int types it works well, but when I try to save DateTime value I get an error message:

Insert into Proxy (Ip, RequisitionDate) 
values ('1', '2016-05-18 3:32:21' )

Error:

RequestError: Validation failed for parameter 'RequisitionDate'. Invalid date.] message: 'Validation failed for parameter \'RequisitionDate\'. Invalid date.', code: 'EPARAM' }

Well, the interesting thing is that

Insert into Proxy (Ip, RequisitionDate) 
values ('1', '2016-05-18 3:32:21')

is the query that i execute in node.js api:

var query = "Insert into Proxy (Ip,RequisitionDate) values ( '"+ ip + "', '"+ date + "' )";
console.log(query); // Insert into Proxy (Ip,RequisitionDate) values ( '1', '2016-05-18 3:32:21' )

request = new Request(query, function(err) {
    if (err) {
        console.log(err);}
    });

    request.addParameter('Ip', TYPES.NVarChar,'SQL Server Express 2014');
    request.addParameter('RequisitionDate', TYPES.DateTime , 'SQLEXPRESS2014');

    connection.execSql(request);
}

If I execute the query direct on the SqlManager Studio, it works ok.

Answer

Gary Liu picture Gary Liu · May 19, 2016

It seems that you haven't set the correct datetime value in addParameter function. According the API reference, the function is used as request.addParameter(name, type, value, [options]).

Please try the following code:

var query = "Insert into Proxy (Ip,RequisitionDate) values ( @ip , @date)";

request = new Request(query, function(err) {
    if (err) {
        console.log(err);}
    });

    request.addParameter('ip', TYPES.NVarChar,'<ip value>');
    request.addParameter('date', TYPES.DateTime , new Date());
// or the set the special date, refer to https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date
//  request.addParameter('RequisitionDate', TYPES.DateTime , new Date(2016,05,19,3,32,21))
    connection.execSql(request);
}

Any further concern, please feel free to let me know.