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

related? https://stackoverflow.com/questions/32958525/datetime-conversion-failed-with-sequelise-inserting-a-sql-server-record — , Nov 01 '17 at 14:11
Thanks, that is related, and works. However, changing the column type may not be an option at this point. Do you know of another way without changing the SQL table definition? A way to pass a valid value from Javascript/Sequelize? — Zach, Nov 01 '17 at 14:24
i dont know sequalize at all.. but based on what you are saying does `new Date().toISOString()` not produce a string like: `2017-11-01T14:50:33.239Z` i would try and format that as YYYY-MM-DD HH:MM:SS (similar to https://stackoverflow.com/questions/5129624/convert-js-date-time-to-mysql-datetime) — , Nov 01 '17 at 14:53

score 26 · Answer 1 · answered Apr 05 '18 at 18:17

26

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');
};

answered Apr 05 '18 at 18:17

Brent Matzelle

4,073
3
28
27

3

This works great, simply instantiate the override before calling `new Sequelize(...)` – GavinBelson Dec 19 '18 at 20:09
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'); };` – hironico Dec 15 '21 at 12:27

Zach · Answer 2 · 2017-11-01T18:40:52.053

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.

score 0 · Answer 3 · answered Jan 04 '21 at 09:19

0

You can use this variable:

const timestamps = new Date() + 3600 * 1000 * 7;

answered Jan 04 '21 at 09:19

Dang Thach Hai

347
3
10

score 0 · Answer 4 · answered Aug 08 '22 at 14:07

I got the same issue. I was able to solve the issue by changing the model as follows. ex:

 create_at: {
        type: 'TIMESTAMP',
        defaultValue: new Date().toISOString(),
        allowNull: false
    },
    update_at: {
        type: 'TIMESTAMP',
        defaultValue:new Date().toISOString(),
        allowNull: false
    }

How to pass a DateTime from NodeJS Sequelize to MSSQL

4 Answers4

Linked