1

When adding a new record into my database I get the error:

Conversion failed when converting date and/or time from character string

The datetime it tries to insert is:

2015-10-05 21:43:57.000 +00:00

It seems that Sequelise also inserts the timezone.

I tried setting the "timezone" to an empty string but this didn't help.

How can I insert a valid SQL Server DATETIME with sequelize?

Model:

    RegDate : {
       type : Tedious.TYPES.DateTime,
       defaultValue: Sequelize.NOW
    }
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Jamie
  • 3,031
  • 5
  • 36
  • 59
  • I never use Sequelize with MSSQL, but the docs says: `Sequelize.DATE // DATETIME for mysql / sqlite, TIMESTAMP WITH TIME ZONE for postgres`. http://docs.sequelizejs.com/en/latest/docs/models-definition/#data-types – BrTkCa Oct 05 '15 at 23:31
  • I answered this in the [How to pass a DateTime from NodeJS Sequelize to MSSQL](https://stackoverflow.com/a/49678951/330110) SO question. – Brent Matzelle Apr 05 '18 at 18:37

1 Answers1

1

Change the Column data type in the DB from DateTime to DateTime2

-- This will fail
begin tran
CREATE TABLE #Temp1 ( CTECol datetime );
insert into #Temp1 select '2015-10-05 21:43:57.000 +00:00'
rollback

-- This will Success
begin tran
CREATE TABLE #Temp1 ( CTECol datetime2 );
insert into #Temp1 select '2015-10-05 21:43:57.000 +00:00'
rollback
OSAMA ORABI
  • 441
  • 1
  • 4
  • 14
  • Hi, I know this is an old thread, but I have a question ... Ok, we can solve the *datetime* problem by changing it to *datetime2*. Now, what about *date* datatype. It is designed to only save a date without a time. Should we need to convert *dates* to *datetime2* just for sequelize to work? If this is true, it seems we can't have just sql server *dates* anymore ... – Manuel Rivera Feb 12 '16 at 15:57
  • Could you give an example of a problem you faced when using date? – OSAMA ORABI Feb 22 '16 at 07:24
  • Hi @OSAMA. It seems I assumed something that was wrong. I tested saving a plain javascript date to a *sql server Date* column and everything worked smoothly. So, the problem arises only with *DateTime sql server* types. In that case, we better use *sequelize literal* to assign the date value. Using just a javascript Date fails in such a case. Thanks and bye ... – Manuel Rivera Feb 22 '16 at 14:25