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

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
– Zach, Commented Nov 1, 2017 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 stackoverflow.com/questions/5129624/…) — user359135
– user359135, Commented Nov 1, 2017 at 14:53

Brent Matzelle · Accepted Answer · 2018-04-05 18:17:23Z

27

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 5, 2018 at 18:17

Brent Matzelle

4,1334 gold badges30 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

GavinBelson Over a year ago

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

hironico Over a year ago

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

Zach · Accepted Answer · 2017-11-01 18:40:52Z

9

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.

edited Nov 1, 2017 at 18:40

answered Nov 1, 2017 at 16:18

Zach

8012 gold badges7 silver badges16 bronze badges

Comments

Dang Thach Hai · Accepted Answer · 2021-01-04 09:19:21Z

0

You can use this variable:

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

answered Jan 4, 2021 at 9:19

Dang Thach Hai

3563 silver badges10 bronze badges

Comments

Susampath · Accepted Answer · 2022-08-08 14:07:41Z

0

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
    }

answered Aug 8, 2022 at 14:07

Susampath

71610 silver badges13 bronze badges

Comments

Silvio Tamaso D'Onofrio · Accepted Answer · 2024-11-26 19:16:14Z

0

Sequelize uses datetimeoffset dataype to insert data into createAt and updatedAt columns.

Simply change the column type of any date / datatime field from existing datetime or datetime2 to datetimeoffset and it will work fine!

Good luck!

edited Nov 26, 2024 at 19:16

Silvio Tamaso D'Onofrio

3,1492 gold badges20 silver badges43 bronze badges

answered Nov 24, 2024 at 15:50

kami

1

Collectives™ on Stack Overflow

How to pass a DateTime from NodeJS Sequelize to MSSQL

5 Answers 5

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related