node.js sequelize.js tedious sequelize-typescript

Inserting or Updating a Date field give the following error Conversion failed when converting date and/or time from character string

I am trying to update/insert a datetime stamp value in a table, I am getting the 'Conversion failed when converting date and/or time from character string':

    return UserTableDto.update({
      'firstName': user.firstName,
      'lastName': user.lastName,
      'LastUpdatedBy': user.lastUpdatedBy,
      'LastUpdatedOn': new Date()
    }).then((userDtoResult: UserDto) => {
      resolve(userDtoResult);
    });

User Model:

import { Table, Column, Model, HasMany, DataType, PrimaryKey, ForeignKey, Default, BelongsToMany, BelongsTo } from 'sequelize-typescript';

@Table({ freezeTableName: true, tableName: 'User', timestamps: false })
export class UserDto extends Model<UserDto> {

    @PrimaryKey
    @Default(DataType.UUIDV4)
    @Column(DataType.UUID)
    userId: string;

    @Column
    firstName: string;

    @Column
    lastName: string;

    @Column
    email: string;

    @Column
    InitCreatedBy: string;

    @Column
    StartDate: Date;

    @Column
    EndDate: Date;

    @Column
    InitCreatedOn: Date;

    @Column
    LastUpdatedBy: string;

    @Column
    LastUpdatedOn: Date;

    @Default(true)
    @Column
    active: boolean;

    @Default(false)
    @Column
    deleted: boolean;

    @Column
    deletedOn: Date;
}

Throws the following exception:

original:
    { RequestError: Conversion failed when converting date and/or time from character string.
     at Parser.tokenStreamParser.on.token (C:\Users\someuser\workspaces\project\node_modules\tedious\lib\connection.js:779:27)
     at emitOne (events.js:116:13)
     at Parser.emit (events.js:211:7)
     at Parser.parser.on.token (C:\Users\someuser\workspaces\project\node_modules\tedious\lib\token\token-stream-parser.js:27:14)
     at emitOne (events.js:116:13)
     at Parser.emit (events.js:211:7)
     at addChunk (C:\Users\someuser\workspaces\project\node_modules\tedious\node_modules\readable-stream\lib\_stream_readable.js:297:12)
     at readableAddChunk (C:\Users\someuser\workspaces\project\node_modules\tedious\node_modules\readable-stream\lib\_stream_readable.js:279:11)
     at Parser.Readable.push (C:\Users\someuser\workspaces\project\node_modules\tedious\node_modules\readable-stream\lib\_stream_readable.js:240:10)
     at Parser.Transform.push (C:\Users\someuser\workspaces\project\node_modules\tedious\node_modules\readable-stream\lib\_stream_transform.js:139:32)
     at doneParsing (C:\Users\someuser\workspaces\project\node_modules\tedious\lib\token\stream-parser.js:80:14)
     at token (C:\Users\someuser\workspaces\project\node_modules\tedious\lib\token\infoerror-token-parser.js:48:5)
     at call.lineNumber (C:\Users\someuser\workspaces\project\node_modules\tedious\lib\token\infoerror-token-parser.js:13:19)
     at awaitData (C:\Users\someuser\workspaces\project\node_modules\tedious\lib\token\stream-parser.js:179:7)
     at Parser.awaitData (C:\Users\someuser\workspaces\project\node_modules\tedious\lib\token\stream-parser.js:103:7)
     at Parser.readUInt32LE (C:\Users\someuser\workspaces\project\node_modules\tedious\lib\token\stream-parser.js:176:10)
     at parser.readBVarChar.procName (C:\Users\someuser\workspaces\project\node_modules\tedious\lib\token\infoerror-token-parser.js:12:90)
     at readBuffer.data (C:\Users\someuser\workspaces\project\node_modules\tedious\lib\token\stream-parser.js:316:9)
     at awaitData (C:\Users\someuser\workspaces\project\node_modules\tedious\lib\token\stream-parser.js:308:7)
     at Parser.awaitData (C:\Users\someuser\workspaces\project\node_modules\tedious\lib\token\stream-parser.js:103:7)
     at Parser.readBuffer (C:\Users\someuser\workspaces\project\node_modules\tedious\lib\token\stream-parser.js:305:10)
     at readUInt8.length (C:\Users\someuser\workspaces\project\node_modules\tedious\lib\token\stream-parser.js:315:12)
     at awaitData (C:\Users\someuser\workspaces\project\node_modules\tedious\lib\token\stream-parser.js:123:7)
     at Parser.awaitData (C:\Users\someuser\workspaces\project\node_modules\tedious\lib\token\stream-parser.js:103:7)
     at Parser.readUInt8 (C:\Users\someuser\workspaces\project\node_modules\tedious\lib\token\stream-parser.js:120:10)
     at Parser.readBVarChar (C:\Users\someuser\workspaces\project\node_modules\tedious\lib\token\stream-parser.js:314:10)
     at parser.readBVarChar.serverName (C:\Users\someuser\workspaces\project\node_modules\tedious\lib\token\infoerror-token-parser.js:11:22)
     at readBuffer.data (C:\Users\someuser\workspaces\project\node_modules\tedious\lib\token\stream-parser.js:316:9)
      message: 'Conversion failed when converting date and/or time from character string.',
      code: 'EREQUEST',
      number: 241,
      state: 1,
      class: 16,
      serverName: 'sqlsvr',
      procName: '',
      lineNumber: 1,
      sql: 'UPDATE [User] SET [LastUpdatedOn]=@0 OUTPUT INSERTED.* WHERE [userId] = @1',
      parameters:
       { '0': '2019-09-20 16:58:52.256 +00:00',
         '1': 'E1000C5A-43E1-4F52-88A1-4EE6A869FE28' } },
   sql: 'UPDATE [User] SET [LastUpdatedOn]=@0 OUTPUT INSERTED.* WHERE [userId] = @1',
   parameters:
    { '0': '2019-09-20 16:58:52.256 +00:00',
      '1': 'E1000C5A-43E1-4F52-88A1-4EE6A869FE28' } }

I tried using new Date().toISOString(), new Date().toUTCString(), new Date().getTime() but still the same error.

Environment:

Sequelize version: 5.19.0
Node.js version: v8.16.0
Operating System: Windows 10 64bit
TypeScript version: 3.3.3333
sequelize-typescript: 1.0.0
tedious: 6.3.0

Created an issue with sequelize: Sequelize Issue 11460

Solution

I have resolved my issue by following:

return UserTableDto.update({
      'firstName': user.firstName,
      'lastName': user.lastName,
      'LastUpdatedBy': user.lastUpdatedBy,
      'LastUpdatedOn': Sequelize.fn('GETDATE')
    }).then((userDtoResult: UserDto) => {
      resolve(userDtoResult);
});

Which will output the following query:

UPDATE [UAM_User] SET [LastUpdatedOn]=GETDATE() OUTPUT INSERTED.* WHERE [userId] = @0

The above example is for the SQL server. If using Oracle or some DB use the function that generates the Date. For example, in MySQL, the function for getting the current time is NOW() so the function would be:

'LastUpdatedOn': Sequelize.fn('NOW')

-----------------------------OR---------------------------------------

Another way is to change the object type from Date to string and pass a formatted date value in "yyyy-MM-dd HH:mm:ss.SSSSSS" format:

@Column
LastUpdatedOn: string; // Changed from Date

Then Passing value to this column need to pass formatted date value:

const d = new Date();
const dt = `${d.getFullYear()}-${d.getUTCMonth() + 1}-${d.getDate()} ${d.getHours()}:${d.getMinutes()}:${d.getSeconds()}.${d.getMilliseconds()}`;

return UserTableDto.update({
      'firstName': user.firstName,
      'lastName': user.lastName,
      'LastUpdatedBy': user.lastUpdatedBy,
      'LastUpdatedOn': dt
    }).then((userDtoResult: UserDto) => {
      resolve(userDtoResult);
});