Occasional Parsing Failure - Date&Time Input and String to Date&Time

[jan_lender]

After upgrading to 4.1.2 I started experiencing occasional failure of a String to Date&Time. I haven’t experienced the issue I’m going to describe in previous versions.

In many cases, I need to work with the current time in my workflows. Mainly to produce some sort of audit metadata like a time some data was processed at or so. Usually, I create a single-row & single-column table containing a timestamp I will use in the subsequent part of the workflow.

This is how I get the table:

The Empty Table Creator creates a one-row table.
Date&Time Input configuration is as follows.

The Constant Value Column node creates new column using just created timestamp_str flow variable.

And last, the String to Date&Time finishes the job converting a string value to a Date&time value.

Well, this has been working very well for me for long months. But after an upgrade to 4.1.2 yesterday, It started to behave in a very strange way. My workflow processes a number of tables initializing timestamp the way I revealed above for each table. It was the workflow processed some of the tables and suddenly it failed. I could see this message in the console:

ERROR String to Date&Time 7:414 Execute failed: Failed to parse date in row 'Row0: Text '2020-03-30T03:13:49.51' could not be parsed, unparsed text found at index 19

Wow! First I thought the issue appeared randomly. After a portion of furious testing, I figured out the problem raises when Date&Time Input node really produces an input that doesn’t match the pattern the String to Date&Time node is configured with.

I could see something like 2020-03-30T03:20:36.234 in most cases.

But I could see something like 2020-03-30T03:20:36.23 in a few cases too.

And this made the String to Date&Time fail. I tried changing the pattern to yyyy-MM-dd’T’HH:mm[:ss[.SS[S]]] to make the last millisecond digit optional but it didn’t work at all.

I don’t say this is a bug but It never happened before the upgrade to 4.1.2. And it is likely going to lead me to check and fix my workflows.

As a workaround, I could sanitize the string introducing a String Manipulation / (Variable) node. The better way is, in my opinion, using the Column Expressions node which is able to manage both variants of the string Date&Time Input can produce. This a configuration of the node that works:

[ipazin]

Hi there @jan_lender,

I remember now I encountered this behavior as well and haven’t found appropriate format with String to Date&Time node! What you can do to avoid this is create “timestamp” (albeit without milliseconds) with Create Date&Time Range. Use execution time and choose to create only one row.

Edit: Actually (just learned tnx to @SimonS) that you can output milliseconds in Crate Date&Time range but first need to add input time with milliseconds included and then check use execution date&time boxes

Br,
Ivan

[SimonS]

Hi @jan_lender,

First of all, the solution @ipazin proposes is the one I would go with. The Create Date&Time Range node does what you are doing using 3 nodes. However, you could also stay with your solution and use the format yyyy-MM-dd'T'HH:mm[:ss[.SSS][.SS][.S]]which is able to parse date&times with zero, one, two, or three millisecond digits.

Cheers,
Simon