Date Formats

[Ramakant_Patil]

I want to change the different date formats into one formats to do further operation.

The column shown in attached image that having 10 different formats.
Please give the solution

[prashant7526]

I guess, it is better if it is possible to fix the source data. From DB perspective anywhere data sitting like this is not correct or follow any standard.

The reason is: Even if you convert the column to List and use Java Snippet, still you need to specify the format. In you case the First three data doesn’t match later data. Most used I can see is (partially speaking): M/D/YYYY, but in first three it is D.MM.YYYY.

I don’t know about other programming but in Java this kind of mixing Date is dangerous. You have to code every logic like splitting, checking which place is above 12 and even if you will use this logic 03.04.2025 the program wont know which one will month and date.

[prashant7526]

If you know that when period or minus sign will come the format will change and the format is constant. You can normalize is through the “Column Expression” Node using condition+split+join (or any additional logic that will apply as per the need). This will be the easiest solution if you know in which case which format appears.

[Ramakant_Patil]

Thanks for the reply.

the data is coming from different locations so not possible for me . i want to extract days from that day column.
ex - Todays date minus available date.

[takbb]

Hi @Ramakant_Patil , with date formats which are sufficiently different that they can be identified with no ambiguity, you can use multiple format masks within a single String to Date&Time node…

See my previous post (below) for an example. You simply place each format sequentially in square brackets but place them in the order that correctly identifies each format. On my mobile phone at the moment so can’t easily write example for your data but if you need further assistance let me know and I’ll take another look when back on my pc…

how to convert 2 date format in one

Hi @nisha_rathi , you’re welcome. The above was the general answer to handling multiple formats but on further thought there was a simpler solution and it could be achieved with a single String to Date&Time node using the following format mask instead: [yyyy-MM-dd][dd-MM-yyyy] The square brackets make parts of the mask optional and so the node would be able to work with both your formats in one go.

By the way, for the dates with month names, will September be Sep or Sept? You’ll need to set the locale correctly to handle that one month. Eg if “Sep” you could set locale to en-US, but if it’s “Sept”, set it to en-GB

[prashant7526]

I hope this will work:

Try below workflow:

86655 - Date Formats – prashant7526

Java Snippet Code:

import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;

//Only work if you know what will be the format if  ".", "-", and "/" will appear.
String dotPattern = "dd.MM.yyyy HH:mm:ss";
String minusPattern = "dd-MMM-yy h:mm:ss a";
String slashPattern = "M/d/yyyy h:mm:ss a";
String outputPattern = "yyyy-MM-dd'T'HH:mm:ss";

DateTimeFormatter outputFormatter = DateTimeFormatter.ofPattern(outputPattern);

if(c_DateTime.contains("."))
{
	DateTimeFormatter inputDotFormatter = DateTimeFormatter.ofPattern(dotPattern);
    LocalDateTime localDotDateTime = LocalDateTime.parse(c_DateTime, inputDotFormatter);
    out_DateTime = localDotDateTime.format(outputFormatter);
}
else if(c_DateTime.contains("-"))
{
	DateTimeFormatter inputMinusFormatter = DateTimeFormatter.ofPattern(minusPattern);
    LocalDateTime localMinusDateTime = LocalDateTime.parse(c_DateTime, inputMinusFormatter);
    out_DateTime = localMinusDateTime.format(outputFormatter);
}
else if(c_DateTime.contains("/"))
{
	DateTimeFormatter inputSlashFormatter = DateTimeFormatter.ofPattern(slashPattern);
    LocalDateTime localSlashDateTime = LocalDateTime.parse(c_DateTime, inputSlashFormatter);
    out_DateTime = localSlashDateTime.format(outputFormatter);
}

[takbb]

Optional format masks, as mentioned, within the String to Date&Time node can handle this.

Set the locale to en-US and the format mask to the following:

[d.M.yyyy HH:mm:ss][dd-MMM-yy h:mm:ss a][M/d/yyyy h:mm:ss a][dd.MM.yyyy HH:mm:ss]

This tries each of the format masks for each date and returns the data according to the first one that matches

In my example I’ve appended the datetime as a new column, but you could use “Replace”, and you could alternatively use a series of 4 String to Date&Time nodes, with each one being given one of the date format masks; in which case you’d un-check the “Fail on Error” option.

[prashant7526]

@takbb This is just WOW I didn’t know this. Thank you.

[Ramakant_Patil]

Thanks for the reply.

But unfortunately not working at my end.
I have attached the Node please check and let me know if anything is wrong with this.

I am having these pattern in column(Value_Date).

[dd.MM.yyyy HH:mm:ss]
[dd-MM-yyyy HH:mm:ss]
[dd-MM-yyyy h.mm.ss AM/PM]
[dd-MM-yy hh:mm:ss AM/PM]
[dd/MM/yyyy HH:mm:ss]
[yyyy-MM-dd HH:mm:ss]
[yyyy/MM/dd HH:mm:ss]
[yyyy/M/d HH:mm:ss]
[mm/dd/yyyy HH:mm:ss]
[mm/dd/yyyy hh:mm:ss AM/PM]

[prashant7526]

@Ramakant_Patil What is the error message when you roll over your mouse on red cross?

[Ramakant_Patil]

Please check attached image

[prashant7526]

Can you please remove Spaces between the conditions and try.

[takbb]

A word of wartning if you use very wide format masks, as it now appears you require…

Because you have so many different formats, this could lead to undesirable future behaviour with the String to Date&Time config, where it becomes overly wide every time you open it. This is because it widens the config to the size of any large recently used format mask.

See the following topic (and subsequent replies) for more details:

Oversized Config dialog on Date&Time to String and String to Date&Time nodes

Hi all, I recently (last day or so) updated my KNIME from 5.2.3 to 5.2.4, although this may be unrelated. This morning, I tried using Date&Time to String nodes and the configuration screen is oversized. It’s never been a problem before, but it seems to be trying to use up probably twice the width of my available screen, and doesn’t resize “nicely” either. This is on Windows 10 This is also happening to String to Date&Time, but interestingly I haven’t seen it on any other nodes. Here is a gif…

You may be advised to use more than one node, and have each handle some of the formats, (with fail on error turned off) and then merge the resultant appended columns into a single column using Column Merger.

[takbb]

@Ramakant_Patil

I am having these pattern in column(Value_Date).

[dd.MM.yyyy HH:mm:ss]
[dd-MM-yyyy HH:mm:ss]
[dd-MM-yyyy h.mm.ss AM/PM]
[dd-MM-yy hh:mm:ss AM/PM]
[dd/MM/yyyy HH:mm:ss]
[yyyy-MM-dd HH:mm:ss]
[yyyy/MM/dd HH:mm:ss]
[yyyy/M/d HH:mm:ss]
[mm/dd/yyyy HH:mm:ss]
[mm/dd/yyyy hh:mm:ss AM/PM]

@Ramakant_Patil , as well as the spaces in your format masks, as @prashant7526 mentions, your final two masks are incorrect as they should be capital M for month:

[MM/dd/yyyy HH:mm:ss]
[MM/dd/yyyy hh:mm:ss AM/PM]

BUT…

This cannot work. If your system is presented with days in the range 1-12, how is it supposed to determine whether it is a MM/dd or a dd/MM date?

e.g
07/02/2025 12:30:00
02/07/2025 18:00:45

I could tell you that one is 2 July and the other is 7 February, but how is KNIME supposed to determine which is which?

You cannot mix ambiguous dates that have similar looking formats, and if they exist you have no option but to go back to the source and determine which they are supposed to be BEFORE mixing them together.

[prashant7526]

I am little nervous now if I might get wrong so forgive me.

@Ramakant_Patil I mention it here:

Date Formats

I guess, it is better if it is possible to fix the source data. From DB perspective anywhere data sitting like this is not correct or follow any standard. The reason is: Even if you convert the column to List and use Java Snippet, still you need to specify the format. In you case the First three data doesn’t match later data. Most used I can see is (partially speaking): M/D/YYYY, but in first three it is D.MM.YYYY. I don’t know about other programming but in Java this kind of mixing Date is d…

And that is the reason I gave the programmatic way of solution:

Date Formats

I hope this will work: [bMWHkQPQ8r] Try below workflow: Java Snippet Code: import java.time.LocalDateTime; import java.time.format.DateTimeFormatter; //Only work if you know what will be the format if ".", "-", and "/" will appear. String dotPattern = "dd.MM.yyyy HH:mm:ss"; String minusPattern = "dd-MMM-yy h:mm:ss a"; String slashPattern = "M/d/yyyy h:mm:ss a"; String outputPattern = "yyyy-MM-dd'T'HH:mm:ss"; DateTimeFormatter outputFormatter = DateTimeFormatter.ofPattern(outputPattern)…

And mentioned:
//Only work if you know what will be the format if “.”, “-”, and “/” will appear.

Because you cannot make Node or Tool to guess which one is month and with one is date in 07/02

But in this post I learnt amazing power of String to DateTime from @takbb . Thank you.

[takbb]

Your java snippet solution was good @prashant7526, and arguably clearer in many ways than potentially fighting with the optional format masks, so it’s really down to personal preference and whatever you find easier.

But you are exactly right that whichever mechanism is used, it can only work if the order of month and day can be determined by the format of the string.

If you and I look at a date and cannot “know” which it is… well… KNIME’s good, but it is not magic

[Ramakant_Patil]

Nothing happens after removing spaces.

Please check attached file having all the formats that i need to convert into same formats.

Value_Date.xlsx (41.7 KB)

[prashant7526]

@Ramakant_Patil Below multiple format appearing with “-”. This is crazy.

For this below are the rules:

1. You cannot use one String to Date&Time Node and fix it all.
2. You have to filter each case out using Row Filter or Rule-based Row Filter Node (It might be possible that you will need nested logics in filtering).
3. Once you filter out each case then you can use String to Date&Time Node to output common format for each cases.
4. And then use Concatenate Node to combine back to one Table.

[MartinDDDD]

Mind blown for today - will need to start getting rid of some python scripts / case switch branches in my existing workflows ASAP

[prashant7526]

Hi @Ramakant_Patil,

I have posted partial solution to give you an idea how to carry it forward. This is really tiresome work. I am just worked on the dates that contains “/”.

There are a lot of cases.

Download below workflow and you will get an idea what you have to do (the workflow is still incomplete).

86655 - Date Formats – prashant7526