How to concatenate date and time

[BasT]

I have data with two columns; a date and a time stamp.
The timestamp is in AM/PM, but the convert functions do not work. AMtoPM parsing gives problems because the data contains both 12AM/PM. The parsing function can only deal with values 0-11.

How can I combine these columns into one column with date + time?

[tqAkshay95]

Hello @BasT ,
You can design a workflow like the attached image and configure the node with reference to the attached images.
you can get the output that you want.

Final Output:

[takbb]

Hi @BasT, welcome to the KNIME community.

If you want the end result as an actual DateTime column, then here is an alternative solution.

There different date and time parsing codes to handle variations of dates and time including AM/PM

To combine the two into a single Date&Time column, first of all convert the DATE into a string using Date&Time to String

The format mask I’m using here is yyyy-MM-dd. Note that it is case-sensitive so capital MM represents months (lower case mm would be “minutes”). I’m replacing the original Date column with a String column.

I’m then using String Manipulation to join the two strings together

joinSep(" ",$ORDER_CREATION_DATE$,$ORDER_CREATION_TIME$)

You could equally use Column Aggregator for a no-code solution

Finally the String to Date&Time can convert it to an actual DateTime column.
The format mask here is
yyyy-MM-dd h:mm:ss a

Once again note that this format mask is case-sensitive. h means hour in range 1-12 and a means AM/PM indicator. The codes are available in the documentation for the node.

Also of note though is the Locale setting. As your AM/PM are UPPER case, use a locale that has this format such as en-US.

If it were lower case am/pm, use a different locale such as en-GB which supports the lower case version. That’s a small annoyance of mine and it won’t be obvious from any documentation.

Edit: Demo Workflow attached

concatenate date and time.knwf (15.6 KB)

[BasT]

Thanks for the quick response. Couple questions about this approach:

• By taking substring of 8 all timings which are lower than 10 are failing. See also screenshot
• It does not recognize that 10:38:26 PM = 22:38:26. The String to Date&Time node also gives error that there is no suitable format found.

[takbb]

Hi @BasT in terms of “no suitable format found”, the list on the Date&Time nodes are not exhaustive and can be overtyped if it doesn’t have one readily available.

[mlauber71]

@takbb , @BasT maybe you could provide sample data and workflows so one could experiment?

[tqAkshay95]

Hello @BasT
Can you provide a sample data?