SQL DateTime format not recognized

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.
2022-11-14_14-19-45

Any ideas ? Thanks

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.

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’.

What does the incoming data look like?

[

{

“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

}

]

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.

Good thinking :stuck_out_tongue: , 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.

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

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

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.

@Jon Running the latest version 0.202.1

@Jon rolling back versions same result

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

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()}}

2 Likes

AHHH ! You are my savior !

Thank you so much ! :slight_smile:

1 Like

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

2 Likes