Hey folks, first time posting but we have been using N8N on the cloud for a while but since the 26th of June we have been encountering issues with various workflows failing due to date values in the data not being handled and resulting in NUlL values or {} being output. Only come to light as one workflow inserts data into a SQL database where NULL values are no allowed.
I have created a simple workflow which replicates the issue with a SQL node to collect data which returns fine followed by a set node to format the data. It is at this point the dates aren’t being handled correctly. One thing I’ve noticed is it returns activity date as a datetime value rather than just the date value which is in the SQL database but other workflows that are failing get the data passed in from another workflow so I don’t think the issue is specifcally related to the SQL node.
Prior to the 26th of June this was all working correctly. I updated our instance to the latest stable version yesterday (we were 2 behind) but no difference to this behaviour.
@bcraig1988 that {} is the tell — the date’s coming through as a DateTime object (luxon), not a string. the Set node preview renders the object so it looks fine, but when it serializes into SQL or gets copied it collapses to {} or null instead of the value.
force it to a string before it leaves the Set node: {{ $json.activity_date.toISODate() }} for yyyy-mm-dd, or .toISO() for the full timestamp. if it errors that its not a datetime, wrap it DateTime.fromISO($json.activity_date).toISODate(). that kills the {} and fixes the datetime-vs-date mismatch too.
if it really started the 26th its a cloud serialization regression, but stringifying unblocks you now regardless.
Thank you for the very quick reply! So I have tried this previously and how one of original workflows is setup (my example is to keep it as simple as possible) and sorry I missed a key point I forgot to mention is that executing the step on it’s own it does format the dates correctly, the issue is when it’s ran all together is when the NULL/{} happens which has only occurred since the 26th, one of these workflows has been in production for over a year with no issue until then.
@bcraig1988 that clue changes it — step works but full run breaks means the date’s fine as an object in memory but collapses to {} when its serialized passing between nodes at runtime. so by the time it hits your Set node its already {}, nothing left to convert — thats why the Set-node fix did nothing in the full run.
fix it at the source: cast the date to text in the SQL query so it leaves as a plain string and never becomes a serialization-fragile object. postgres activity_date::text or TO_CHAR(activity_date,‘YYYY-MM-DD’), mysql DATE_FORMAT(activity_date,‘%Y-%m-%d’). flows through as a string the whole way.
the serialization change is almost certainly your june-26 regression, casting in the query just dodges it.
Thank you, so casting the activity_date does seem to work in my example and it also fixes the last_updated value ending up {} as a consequence (That is just a GETDATE() at the SQL node).
Is this a “bug” that’s likely to be fixed or a change in behavior we need to adjust any flow impacted?
Thanks that makes sense on the casting, yea I was a little stumped when I couldn’t find anything else that had been reported thinking it was something we had done but I’ll get it raised on GitHub and go from there. Appreciate the help and quick replies!
Had a reply to say it’s now resolved as of version