I am writing a NodeJS application which uses SQL Server as a data store. Node connects to SQL Server using the mssql native driver.
When I select a date object from the database with an empty time field, the date before the stored date is being returned. For example, if the date field is 10/5/2005, then 10/4/2005 is returned.
Here are full details to reproduce:
First, create a table with a datetime column:
/****** Object: Table [dbo].[testDate] Script Date: 8/28/2014 8:11:35 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[testDate](
[testID] [int] IDENTITY(1,1) NOT NULL,
[testDate] [datetime] NULL
) ON [PRIMARY]
GO
Then I can use this NodeJS code to demonstrate the problem. It creates a new record in the testDate table, selects the new record, then uses console.log to output the date column.
This is the insert query:
insert into testDate (testDate) values ('10/5/2005')
Then run a select query:
select *from testDate
where testID = 1
Which will return the new row. Something like this:
testID: 1
testDate: 2005-10-05 00:00:00.000
The date has zero for the time stamp values--which is expected--and 10/5/2005 as the date--which also makes sense. When this item is selected using the mssql driver inside of Node, it turns into this:
Tue Oct 04 2005 20:00:00 GMT-0400 (Eastern Daylight Time)
Basically, giving me 10/4/2005 instead of 10/5/2005. I've tried this with multiple dates and am able to consistently replicate the issue.
What am I doing wrong?
For sake of completeness;
Here is a full semi-runnable NodeJS code snippet which will insert a new record, select it, and output the date column to the console:
// import the db driver
var sql = require("mssql");
// set up the config to access the database
var config = {
user: 'sa',
password: 'whatever',
server: 'myserver.com',
database: 'testDB'
};
// Query to insert into database
var query= "insert into testDate (testDate) values ('10/5/2005')";
var query= query + " SELECT SCOPE_IDENTITY() as testID";
console.log(query);
// create connection
var connection = new sql.Connection(config, function(err) {
// when connection is made trigger off the first query
var request = new sql.Request(connection);
request.query(query, function(err, recordset){
// result handler for query 1
console.log(err); // undefined if everything is set up right
console.log(recordset[0].testID); // the ID of the record we just inserted
// select the item we just created from the database
var query2= "select * from testDate where testID = " + recordset[0].testID;
console.log(query2);
// execute second query
var connection = new sql.Connection(config, function(err) {
var request = new sql.Request(connection);
request.query(query2, function(err, recordset){
console.log(err); // undefined if everything is set up right
console.log(recordset[0].testDate); // The Date; which is returning "Tue Oct 04 2005 20:00:00 GMT-0400 (Eastern Daylight Time" instead of Wednesday 10/5/2005
});
})
});
})
At the moment, I am assuming I am doing something wrong as opposed to this being a bug in the mssql driver; but I'd be more than happy to open an issue on the mssql github account if no one can point out what I am doing wrong.