SQL DateTime format not recognized

[skalg]

Hi, I’m trying to move data between two database. One is MySQL and the other Microsoft SQL.
When I try to update a row with a DateTime i’m getting the error

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

I’ve tried to set a new date().toSTRING(), and/or to CAST in a DATETIME, nothing’s working. If I copy paste the “result” in Microsoft SQL and run the row update, it’s working. It looks like N8N does not paste the date as is.

Any ideas ? Thanks

[Jon]

Hey @skalg,

We should be sending the data as it is in the result window there, Do you only have the one input item or is there multiple? Have you tried removing the ' ' from the date as well? I can’t remember if MSSQL has issues with that.

[skalg]

Only one input and if I remove the single quotes, it looks like it’s converting the date to a MM date format because it’s now seeing “NOV” as the month… But my string is still in mm format

“ActualArrival”:
“2022-11-11T16:54:40.000Z”,

ERROR: Incorrect syntax near ‘Nov’.

[Jon]

What does the incoming data look like?

[skalg]

[

{

“id”:22,

“CNTR”:“t15487”,

“DELTYPE”:1,

“itemnum”:null,

“ActualArrival”:“2022-11-11T16:54:40.000Z”,

“ActualDeparture”:“2022-11-11T17:01:14.000Z”,

“Notes”:“test”,

“METER”:null,

“photo”:null,

“userid”:75,

“lastmod”:“2022-11-11T17:05:51.000Z”,

“EXPORTED”:0

}

]

[Jon]

That looks ok to me, I would have expected CAST to work. What happens if you make a test table and set the field to be varchar / text then write the value out? It would be interesting to see what is sent, You could also find this in the tsql log in ssms.

[skalg]

Good thinking , So,

If i send it in single quotes to M$ SQL in a varchar field, it gets converted to full datetime.
'{{$json["ActualArrival"]}}' → Fri Nov 11 2022 16:54:40 GMT+0000 (Coordinated Universal Time)

Without single quotes :
ERROR: Incorrect syntax near 'Nov'.

With Cast :
CAST('{{$json["ActualArrival"]}}' AS datetime) → ERROR: Conversion failed when converting date and/or time from character string.

With Regex in Cast, it still converting the datetime in another format :
CAST('{{$json["ActualArrival"].replace(/Z/g,'').replace(/T/g,' ')}}' AS datetime) → Jan 1 1900 12:00AM

And if I keep the regex and remove the Cast, the flow is proceeding but no ouput in the SQL row, it’s blank
UPDATE temp SET Notes = '{{$json["ActualArrival"].replace(/Z/g,'').replace(/T/g,' ')}}'

I’ve also tried .toString() and same result…

And I just tried on a MYSQL DB and it’s working… So it looks like it’s something on my SQL server or with n8n that does some data manipulation with the SQL connector.

[odarkthirty]

This is the same exact problem I am having @Jon on ticket Fails on automated execution but works manually

@skalg when you run the flow manual from the execution window NOT the full automated does it work? or do you still get an error

[odarkthirty]

FYI I am on MySQL and Postgres - so this is not just isolated to MSSQL

[Jon]

I had forgotten about this one, What version of n8n are you both running? It might be worth going back a couple of versions for now.

[odarkthirty]

@Jon Running the latest version 0.202.1

[odarkthirty]

@Jon rolling back versions same result

[odarkthirty]

Sorry for all the messages, but this is pretty major in my view

Seems somehow also related to this: Inconsistency with date serialization between manual and auto execution of Database Queries [MySQL, Postgres] · Issue #3101 · n8n-io/n8n · GitHub

FIX

@skalg I have a fix for you

I haz a fix for you:

{{new Date($json['YOUR_FIELD'].toString()).toISOString()}}

[skalg]

AHHH ! You are my savior !

Thank you so much !

[odarkthirty]

Awesome! Glad it worked can you mark it as the solution so others find it too?