Different date format in one Column

[angelmilo]

Hello Knime Community,

I have a string Date Column with 2 different date&time format. How can I transform it to just 1 date&time format? I want the format to be the 2nd one which is the 21-07-2021 09:04:39

[bruno29a]

Hi @angelmilo and welcome to the Knime Community.

You probably need to first manually format them via the String Manipulation node, and then you can convert the column to a datetime type

You can simply replace the “/” by a “-”. You can use this expression in the String Manipulation node:
replace($sys_updated_on$, "/", "-")

[angelmilo]

Hi @bruno29a,

They have different date format. the first one is M/d/yyyy while the other one is dd-mm-yyyy. If I replace “/” to “-”, it will only become m-d-yyyy and not dd-mm-yyyy.

[angelmilo]

Hi @armingrudd,

I saw your answer to RE: [How do i convert different date format values to similar format in single column. May I also ask for your help on this?

[armingrudd]

Hi @angelmilo and welcome to KNIME Community Forum,

Your first step would be using the Date Extractor node from the Palladian extension:

Date Extractor

This node automatically detects the date and time format of each cell and converts string to date and time.

Now, you have said you want the format the same as the second one, so your next step would be adding Date&Time to String and use this format in the configuration of the node:
dd-MM-yyyy HH:mm:ss

There is another approach to do this using regex in String Manipulation but I recommend the solution above for this case.

[Daniel_Weikert]

What tells you month and day is shifted? Without an idication that is quite hard to figure out for any logic
br

[bruno29a]

Hi @angelmilo , sorry for the delay, a bit busy with work.

To @Daniel_Weikert 's point, how do we know which order is dd and mm in the string? Is it the input data with slash, it’s always M/d/yyyy, and with the dash, it’s always already in dd-mm-yyyy?

[bruno29a]

Hi @angelmilo , if it’s related to the file that you gave in your other thread of:

String to Date&Time Node not working

Hello Community, I wanted to transform column “sys_updated_on” from string to date&time but it’s not working. Here’s the workflow for testing. Purchasing MyJTI.knwf (60.0 KB)

I’ve checked and splitted all the dates with slash, and let’s say the values are in the format of A/B/C, the values of A or B never go beyond 12, so you can’t really tell which of A or B is the day or the month.

The fact that neither ever go beyond 12 makes me suspect that it could be that in some cases it’s M/d/yyyy and in other cases it could be d/M/yyyy…

We just need that you confirm that input data with slash, is M/d/yyyy as you mentioned, and always M/d/yyyy

[bruno29a]

Hi @angelmilo ,

I put something together based on the assumption that data with slash, is M/d/yyyy, meaning I have to swap the position of M and d when converting them to dd-MM-yyyy.

This is what the workflow looks like:

Results after the manipulation:

You can see the original input data in the left column, and you can see for the dates with slash, they are properly formatted, including their time.

Results after converting the manipulated data to type datetime:

As you can see, no problem from Knime, all of them got converted.

Note: I filtered out the other columns so it’s easier to show the targeted data. You can just remove the Column Filter node labelled as “Keeping only the sys_updated_on column” for your use.

Here’s the workflow: Different date formats in one column.knwf (2.3 MB)

[angelmilo]

Hi @bruno29a,

Thanks for providing workaround on this! I’ll check the flow on my end and will update you on the result.

[angelmilo]

Hello everyone! @bruno29a and @armingrudd,

I was able to implement and complete my workflow with @bruno29a workaround.
To provide feedback with @armingrudd suggestion, the date extractor was not able to detect the difference in the format. For example when I use the date extractor node to dd/mm/yyyy format it returns mm-dd-yyyy instead of dd-mm-yyyy which is hard to detect when combined with dd-mm-yyyy format.

Thank you so much to all of you for your immediate help! I am closing this thread by accepting @bruno29a solution! Kudos!

[armingrudd]

I’m glad you could find a solution for your problem here. My solution was given regarding the information provided by you in the topic. I didn’t know you have dd/MM/yyyy format as well as M/d/yyyy. In that case, as @bruno29a and @Daniel_Weikert have mentioned, it becomes tricky to guess which part is M or d. I had the same issue when I was working on a date converter component and wanted to support different formats. My solution there was to check if one of the parts are greater than 12 and pick it as d. I’m curious to know how you exactly you solved this but anyway, I’m happy for you.