How to pass a DateTime from NodeJS Sequelize to MSSQL

Question

I have a NodeJS project, and I am trying to pass an 'UpdateDate' field using Sequelize. I am receiving the error 'Conversion failed when converting date and/or time from character string'. I have tried passing a few different things:

Date.now()
new Date().toISOString()

Neither work. Am I missing something simple? I cannot change the column definition on the table. As far as I know, passing a string such as '2016-05-23 10:39:21.000' to a SQL DateTime field works in SSMS, but it seems to be an issue when using Sequelize and Node.

Thanks

Zach

Solution 1

This is caused by a known issue in Sequelize. The solution is to patch Sequelize's date to string format implementation, like explained here, so that all dates are handled properly. Below is the code that fixes the error.

const Sequelize = require('sequelize');

// Override timezone formatting for MSSQL
Sequelize.DATE.prototype._stringify = function _stringify(date, options) {
  return this._applyTimezone(date, options).format('YYYY-MM-DD HH:mm:ss.SSS');
};

Solution 2

I figured this out, without changing the data type in the SQL database.

In my Model, I had my column defined as DataTypes.DATE, which, according to the Sequelize documentation, is the equivalent of a DateTime in SQL. However, this was throwing the error. When I changed the definition to DataTypes.STRING, and then added this:

var normalizedDate = new Date(Date.now()).toISOString();

normalizedDate now passes through to the DateTime column in SQL without a problem. The issue that I can tell, is Sequelize was adding a time zone to the Date before passing it. Such as, a date like:

'2017-11-01 16:00:49.349'

was being passed through as:

'2017-11-01 16:00:49.349 +00:00'

and it looks like SQL server does not like the '+00:00'.

I hope this helps others.

Comments

[GavinBelson]

This works great, simply instantiate the override before calling new Sequelize(...)

[hironico]

I adapted this answer with sequelize 6.12.0-beta.1 (onward) with type script, you should use : DataTypes.DATE.prototype._stringify = function _stringify(date: Date, options: any) { return this._applyTimezone(date, options).format('YYYY-MM-DD HH:mm:ss.SSS'); };