2

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 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);
Zlatko Loa
  • 149
  • 1
  • 5
  • 13
  • Don't forget you can do `WHERE DateCreated BETWEEN ? AND ?` instead of having to spell it out like this. – tadman Nov 28 '17 at 19:01
  • Which SQL driver are you using? [Sequelize](http://docs.sequelizejs.com) makes this pretty straight-forward and should work with SQL Server. – tadman Nov 28 '17 at 19:02
  • i am using mssql. @tadman can you show me an example in that document? I can't find any.. – Zlatko Loa Nov 28 '17 at 19:03
  • @tadman, and I really don't know about Sequelizer and how it works.. – Zlatko Loa Nov 28 '17 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 Nov 28 '17 at 19:56

1 Answers1

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')
ztech
  • 560
  • 5
  • 13
  • Yes, I did look up on this. But how should I do for that specific case? – Zlatko Loa Nov 28 '17 at 19:35
  • 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' – ztech Nov 28 '17 at 19:40
  • 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 Nov 28 '17 at 19:53
  • 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 Nov 28 '17 at 20:16
  • 1
    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 – Zlatko Loa Nov 28 '17 at 20:29
  • Can confirm that months are zero indexed as far as Javascript date objects are concerned 0 = Jan, 1 = Feb ... https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date – ztech Nov 28 '17 at 20:32
  • Thanks! But do you know why this defaults to 5:00:00 for time? – Zlatko Loa Nov 28 '17 at 20:39