Bulk upload with a DateTime2 column

[rtjrk]

I am attempting to perform a bulk upload on a table that requires the additional precision that's included in DateTime2.

Expected behaviour:

Upon bulk insert, I expect the time component of my DateTime2 column to extend to my desired decimal place (6).

Actual behaviour:

A bulk insert instead converts the date to a JavaScript Date object, causing the time component to round to 3 places.

If I perform a direct insert query for a single line (taking the first row of my Table object), everything works as expected.

I was able to trace this to the Table.prototype._makeBulk function in mssql/lib/table.js. The switch resets the value of the row to a Date object for the types of Date, DateTime, and DateTime2, which ultimately cause the truncated seconds decimals.

Software versions

• NodeJS: 18
• node-mssql: 10.0.2
• SQL Server: SQLEXPRESS

Test function to verify

Upon running, values will be truncated to 00:30:19.4980000 instead of maintaining the full precision of 00:30:19.4982070

async function testingBulk() {
    try {
    const conn = await self.connect(config);

        const table = new sqldb.Table('TestTable');
        table.create = true;
        table.columns.add('ID', sqldb.Int, { nullable: false });
        table.columns.add('TestDateTime2', sqldb.DateTime2(7), { nullable: false });

        for (let i = 0; i < 1000000; i++) {
            table.rows.add(i, '2024-05-09 00:30:19.4982070'); // Test datetime2 value
        }

    console.log('About to test the bulk insert with dummy data')
        await conn.request().bulk(table);

        console.log('Bulk insert completed');
    } catch (err) {
        console.error('Bulk insert failed:', err);
    }
}

testingBulk();

[rtjrk]

I can update with more info if needed, but I ultimately was able to correct this. Leaving open for the time being - unsure the best practice on closing considering this is still an issue without modification.

The table.js script (node_modules/mssql/lib/table.js) was an issue with casting the date to a value that truncates past 3 decimal points in the _makeBulk function. I split DateTime2 from the Date and Datetime cases and did an assessment on the value, but did not reset the row's value to the casted date object (leaving it as a string).

I also had to touch up the datetime2.js within Tedious (node_modules/tedious/lib/data-types/datetime2.js) to also allow a string value for a date. These two changes allowed bulk updates with precision.

[dhensby]

It's not particularly clear or user-friendly, but the way to get the extra precision is to provide a Date object with a custom prop value nanosecondDelta which would be the nanoseconds value.

Now, I agree that if a string is supplied, that the library should probably do that for you, so the idiosyncrasy of tedious is not of concern to the developer when a string is provided - the only problem is that involves date-parsing which is hard ™️

For the timebeing, I believe you can work around this issue like so:

async function testingBulk() {
    try {
    const conn = await self.connect(config);

        const table = new sqldb.Table('TestTable');
        table.create = true;
        table.columns.add('ID', sqldb.Int, { nullable: false });
        table.columns.add('TestDateTime2', sqldb.DateTime2(7), { nullable: false });
+        const date = new Date('2024-05-09 00:30:19.498');  // Test datetime2 value
+        date. nanosecondDelta = 0.000207;

        for (let i = 0; i < 1000000; i++) {
-            table.rows.add(i, '2024-05-09 00:30:19.4982070'); // Test datetime2 value
+            table.rows.add(i, date);
        }

    console.log('About to test the bulk insert with dummy data')
        await conn.request().bulk(table);

        console.log('Bulk insert completed');
    } catch (err) {
        console.error('Bulk insert failed:', err);
    }
}

testingBulk();

[rtjrk]

Thanks for the response. Confirming that was the missing piece.

I'm a little uneasy relying on this prop's existence, but it is an easier approach than what I was doing (modified _makeBulk and some additional logic for a string-based date in datetime2.js within Tedious), aka the difficult date-parsing.