Using dates when querying MSSQL from node.js

Question

I'm trying to query MSSQL from node.js and the query involves dates.

I set my query-date like this:

var datoen = new Date();
datoen.setHours(2,0,0,0);

First of all - on my server this logs out as: 2019-07-03T00:00:00.000Z

Why does it not log out as: 2019-07-03T02:00:00.000Z?

Anyway - that's not really the question. It's the first date format I want and it is identical to the format in the database.

But when I run this query (using mssql from npm):

request.query('select * from tblPriceRooms where 
BarDate = ' + datoen, function (err, recordset) {
        if (err) console.log(err)
        res.send(recordset)

... the server provides this error -->

     info:
  { number: 102,
    state: 1,
    class: 15,
    message: 'Incorrect syntax near \'Jul\'.',
    serverName: 'SERVERNAME\\SQLEXPRESS',
    procName: '',
    lineNumber: 1,
    name: 'ERROR',
    event: 'errorMessage' } },
    name: 'RequestError',
    precedingErrors: [] }

I know the connection works fine. As long as I do not try to query based on dates - I get all the results in the world.

I'm a novice and dates do my head in some times. Any pointers would be highly appreciated!

Don't inject your value, parametrise your query; then your value will be passed as a date/time datatype and will be parsed correctly. — Thom A
– Thom A ♦, Commented Jul 3, 2019 at 10:58
Because you set local hours and the timestamp is UTC, presumably your system is set for +0200. Try datoen.setUTCHours(2,0,0,0). ;-) — RobG
– RobG, Commented Jul 3, 2019 at 11:00
Is it that "simple" @Larnu? I was going to figure out how to parametrise - as soon as I had the basic queries down :-) I guess I'll have to jump right into that then. Thanks a lot! — Jonas__G
– Jonas__G, Commented Jul 3, 2019 at 11:03

Terry Lennox · Accepted Answer · 2019-07-03 11:30:27Z

1

I'd suggest using parameters for the query, you get a lot of benefits from this approach. This should work for you:

// Put whatever date you wish here..
const date = new Date();
date.setUTCHours(2, 0, 0, 0);
request.query('select * from tblPriceRooms where BarDate > @date', (err, result) => {
    if (err) {
        console.error("Error occurred: ", err);
    } else {
        console.info("Rows: ", result);
    }
}).input('date', sql.DateTime, date); // Add the date parameter here

edited Jul 3, 2019 at 11:30

answered Jul 3, 2019 at 11:24

Terry Lennox

30.9k5 gold badges36 silver badges45 bronze badges

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

2 Comments

Jonas__G Over a year ago

Yep, Terry Lennox - that pretty much did the trick :-) Thanks a million!

Terry Lennox Over a year ago

Great, glad to hear it!!

Collectives™ on Stack Overflow

Using dates when querying MSSQL from node.js

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related