typeorm: `node-mssql` v6 incorrectly stores Dates

I’m not sure about the exact issue you are running into but are you aware that mysql (driver) will attempt to do timezone conversion automatically. Perhaps this is contributing to the confusion. There is a timezone option which defaults to local. I have no idea in what case someone would find that helpful but that’s the default. If you set timezone: 'Z' then it will default to UTC.
https://github.com/typeorm/typeorm/blob/master/docs/connection-options.md#mysql--mariadb-connection-options

We have installed the versions for testing:

• MSSQL 6.2.3 (latest on npm is 6.2.3)
• Tedious 6.7.0 (latest on npm is 9.2.1 🤔 Looks like that’s only used by the alpha release of mssql 7.0?)

We do have tests that verify the various column types work for an identity check. Howeer, looks like some of them had been commented out - didn’t see that before.

Bummer, because that’s what I was referring to for tests.

---

I created a test specifically for this issue. datetime works as expected for me - the value is sent over correctly & everything works as expected. datetime2 doesn’t for the first 3 seconds of the day for me.

Ok - so the issue is in datetime2.

Let’s look at the client: It looks like we’re binding the right date to the MSSQL client & doing everything as expected for the query but it fails when the server constructs it all somehow, and passes over the wire.

If I change the type we’re binding it as - manually forcing all datetime2 values to be parameterized as datetime it all starts working as expected. Further pointing things at datetime2 being a problem.

To take TypeORM out of the equation, I’ve set up a minimal reproduction

const mssql = require('mssql');

const connection = new mssql.ConnectionPool({
    server: 'localhost',
    database: 'tempdb',
    options: {
      useUTC: false,
    },
    authentication: {
        type: 'default',
        options: {
            userName: 'sa',
            password: 'Admin12345'
        }
    }
});

connection.connect(() => {
    const request = new mssql.Request(connection);
    const testDatetime = new Date(2018, 11, 1, 0, 0, 0, 0);
    request.input(0, mssql.Int, Math.floor(Math.random() * 9999999));
    request.input(1, mssql.DateTime, testDatetime);
    request.input(2, mssql.DateTime2(), testDatetime);
    request.query("INSERT INTO \"test\" (id, testDatetime, testDatetime2) VALUES (@0, @1, @2);", console.log);
});

This ALSO exhibits the issue which means that this is not a bug in TypeORM but in node-mssql or tedious

Digging further, the datetime2 code in tedious does the following:

        const dstDiff = -(parameter.value.getTimezoneOffset() - YEAR_ONE.getTimezoneOffset()) * 60 * 1000;
        buffer.writeUInt24LE(Math.floor((+parameter.value - +YEAR_ONE + dstDiff) / 86400000));

If you replace parameter.value with our Dates:

const YEAR_ONE = new Date(2000, 0, -730118);

for (let i = 0; i < 5; i++) {
  const d = new Date(2018, 11, 1, 0, 0, i, 0);
  const dstDiff = -(d.getTimezoneOffset() - YEAR_ONE.getTimezoneOffset()) * 60 * 1000;
  console.log(Math.floor((+d - +YEAR_ONE + dstDiff) / 86400000));
}

You get the following outputs:

737027
737027
737028
737028
737028

This is apparently where this bug is - this is how we end up with the wrong date.

The rounding causes the date to be off by one. Thing is… this code was changed by the latest release of tedious. Experimenting with the next release means updating to mssql 7.0.0-alpha.1 - which I did for the test. A few small changes needed but otherwise easy to get working.

And in testing - this fixes the problem. I think that can confirm that this is a bug in Tedious. The PR that fixed it in tedious is https://github.com/tediousjs/tedious/pull/1023

Problem is, though - we cannot install that alpha release & use it because it’s not compatible with the version we’re using now. Until it’s out of alpha I’m not comfortable coding against it, either.

---

@pburrows Does that help at all?

Previous error dissapear, but now it shows:

TypeError [ERR_INVALID_ARG_TYPE]: The "string" argument must be of type string or an instance of Buffer or ArrayBuffer. Received type number (0)

when I try to do a DB operation (the login SELECT in this case)

Tests for mssql are now passing in CI. Any time we use the older mssql module it acts up like this. Might be a good place to look for debugging.

We’ve confirmed it as working in our CI environment and I’ll be closing this out.