SQL Server date range search issue-CodePudding

I have a MS SQL Server DateTime field, and Im trying to search all records that are in between a date range:

mySqlString = "select * from users where signupDate >=@from and signupdate <=@to"

The two variables containing the date range come with format MM/dd/yyyy (dataFrom and dataTo, so Im replacing @from and @to at the string as follows:

    datefrom = new Date(dataFrom);
    dateto = new Date(dataTo);
    req.input('from', sql.DateTime, datefrom )
    req.input('to', sql.DateTime, dateto )

But I do not get any result.

What's the best approach to get this working properly?

CodePudding user response：

You can always use CONVERT to accommodate your SQL query to your input format. In your case its format 101: select convert(varchar, getdate(), 101) ---> mm/dd/yyyy

So your query should look like

where (signupdate >= CONVERT(date, @from, 101)) AND (signupdate <= CONVERT(date, @to, 101))

This way you won't worry about the time of the stored date

req.input('from', sql.Date, (dataFrom))
req.input('to', sql.Date, (dataTo))

Assuming you checked if dataFrom and dataTo have valid dates.