sequelize: Date format used for MSSQL dialect appears to be incorrect

Sequelize translates the Sequelize.DATE type into DATETIME2 for MSSQL, but then proceeds to format dates in this format: 'YYYY-MM-DD HH:mm:ss.SSS Z'. MSSQL’s formats are as follows:

Data Type	Format
`datetime`	`YYYY-MM-DD hh:mm:ss[.nnn]`
`datetime2`	`YYYY-MM-DD hh:mm:ss[.nnnnnnn]`
`datetimeoffset`	`YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+

(from https://msdn.microsoft.com/en-us/library/ms186724.aspx )

Since sequelize is including the offset when it shouldn’t be, was a datetimeoffset type expected?

About this issue

Original URL
State: closed
Created 9 years ago
Comments: 19 (6 by maintainers)

Most upvoted comments

something like this:

  [sequelize.fn('date_format', sequelize.col('date_col'), '%Y-%m-%d'), 'date_col_formed']],

zeeshanjan82 on Oct 29, 2015

Fixed in https://github.com/sequelize/sequelize/pull/5274 to use YYYY-MM-DD hh:mm:ss[.nnn]

(Yes, I know that datetime2 theoretically offers 6 digits of ms precision, but javascript doesn’t 😄)

janmeier on Jan 20, 2016

Sequelize currently has this in sql-string.js:

SqlString.dateToString = function(date, timeZone, dialect) {
  if (moment.tz.zone(timeZone)) {
    date = moment(date).tz(timeZone);
  } else {
    date = moment(date).utcOffset(timeZone);
  }

  if (dialect === 'mysql' || dialect === 'mariadb') {
    return date.format('YYYY-MM-DD HH:mm:ss');
  } else {
    // ZZ here means current timezone, _not_ UTC
    return date.format('YYYY-MM-DD HH:mm:ss.SSS Z'); /// <-- Executed for MSSQL - includes offset
  }
};

And this in dialects/mssql/data-types.js:

DATE.prototype.toSql = function() {
  return 'DATETIME2';
};

Perhaps one should be changed to align with the other? (I’m not sure if there are any other areas that could be impacted by the change)

zeiphon on Jun 14, 2015