javascript 如何将日期时间从 NodeJS Sequelize 传递到 MSSQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/47056395/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to pass a DateTime from NodeJS Sequelize to MSSQL
提问by Zach
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:
我有一个 NodeJS 项目，我正在尝试使用 Sequelize 传递一个“UpdateDate”字段。我收到错误消息“从字符串转换日期和/或时间时转换失败”。我试过传递一些不同的东西：
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.
都不工作。我错过了一些简单的东西吗？我无法更改表上的列定义。据我所知，将诸如 '2016-05-23 10:39:21.000' 之类的字符串传递给 SQL DateTime 字段在 SSMS 中有效，但在使用 Sequelize 和 Node.js 时似乎是一个问题。
Thanks
谢谢
Zach
扎克
回答by Brent Matzelle
This is caused by a known issuein 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.
这是由Sequelize 中的一个已知问题引起的。解决方案是将 Sequelize 的日期修补为字符串格式的实现，如解释here，以便正确处理所有日期。下面是修复错误的代码。
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');
};
回答by Zach
I figured this out, without changing the data type in the SQL database.
我想通了这一点，而无需更改 SQL 数据库中的数据类型。
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:
在我的模型中，我将我的列定义为 DataTypes.DATE，根据 Sequelize 文档，它相当于 SQL 中的 DateTime。但是，这是抛出错误。当我将定义更改为 DataTypes.STRING，然后添加以下内容时：
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:
normalizedDate 现在可以毫无问题地传递到 SQL 中的 DateTime 列。我可以说的问题是，Sequelize 在传递 Date 之前向它添加了一个时区。例如，像这样的日期：
'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'.
看起来 SQL Server 不喜欢“+00:00”。
I hope this helps others.
我希望这对其他人有帮助。
