Passing a node.js parameter in sql query

Question

I have some date field receiving from client side. Basically, I would like to search for this date in my SQL db. How should I pass year, month, and date in the query? I just want to replace the date with the new date I received from client-side.

How do I achieve this using mssql driver? https://www.npmjs.com/package/mssql

node.js

  console.log(date.year);//2017 
  console.log(date.month);//11
  console.log(date.day);//28 

  const pool = new sql.ConnectionPool(config, err => {
    if (err)
      console.log(err);
    else
      console.log("Connection successful");

    pool.request()
      .query(`SELECT * FROM testdB WHERE DateCreated >= '11-28-2017 12:55:00' and DateCreated <='11-28-2017 12:57:00' 
                                      ORDER BY DateCreated DESC`

sample data in db

updated1

var d_lower = new Date(date.year, date.month, date.day, 7,55,0);
var d_upper = new Date(date.year, date.month, date.day, 8,10,0);
pool.request()
  .input('dateParam_lower', sql.Date, d_lower).input('dateParam_upper', sql.Date,d_upper )
  .query('SELECT * from testdB WHERE DateCreated BETWEEN  @dateParam_lower and @dateParam_upper', (err, result) => {
    if (err)
      console.log(err);
    else
      console.log(d_lower);
      console.log(d_upper);
      console.log(result);

Don't forget you can do WHERE DateCreated BETWEEN ? AND ? instead of having to spell it out like this. — tadman
– tadman, Commented Nov 28, 2017 at 19:01
Which SQL driver are you using? Sequelize makes this pretty straight-forward and should work with SQL Server. — tadman
– tadman, Commented Nov 28, 2017 at 19:02
i am using mssql. @tadman can you show me an example in that document? I can't find any.. — Zlatko Loa
– Zlatko Loa, Commented Nov 28, 2017 at 19:03
@tadman, and I really don't know about Sequelizer and how it works.. — Zlatko Loa
– Zlatko Loa, Commented Nov 28, 2017 at 19:11
That's why I linked to the documentation. It's full of examples and demonstrations on how it works, plus there's a multitude of other sources of introduction material. — tadman
– tadman, Commented Nov 28, 2017 at 19:56

ztech · Accepted Answer · 2017-11-28 19:33:31Z

4

Per the docs you should use the input function to map your javascript date data type to a sql date data type

https://www.npmjs.com/package/mssql#input-name-type-value

There is a pretty clear example here

https://www.npmjs.com/package/mssql#promises

return pool.request()
  .input('my_date_param', sql.Date, date_param_variable_name)
  .query('select * from mytable where created_at > @my_date_param')

answered Nov 28, 2017 at 19:33

ztech

5706 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Zlatko Loa Over a year ago

Yes, I did look up on this. But how should I do for that specific case?

ztech Over a year ago

If I understand your question, the answer is that you should not pass year month and date into your query, but rather create a date object from these components and pass that into your query. var d = new Date(year, month, day, hours, minutes, seconds, milliseconds); In the above code you would replace 'date_param_variable_name' with 'd'

Zlatko Loa Over a year ago

please refer to updated code at the very bottom of post. I changed as per your suggestion, but don't get any result. All I want to do is to get all data from my database in the date I am passing

Zlatko Loa Over a year ago

no.. it still does not return anything. Please refer to updated code. I modified again in order to set the range of time. I think it's because it does not match with date format in my db. In my db, i don't have "T" . I also posted how my console looks like. And also, I am not sure why sql.Date() defaults the time to 5:00:00, so I had to add offset to the time I defined.

Zlatko Loa Over a year ago

I can confirm this, it's nothing to do with "T" but rather it is caused by the month being added 1. So If I pass november, it will select december somehow.. i think if i fix this it will solve this problem

|

Collectives™ on Stack Overflow

Passing a node.js parameter in sql query

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related