Mess with datetime format

[Karlygash]

Hello, dear Knimers=)

Can you help me with this date format

I tried dd-MMM-yy, but it is not working
Thanks in advance!

[aworker]

Hi @Karlygash

Welcome back !

Dates can come in many different known formats and this can be customized with KNIME in the Date format field of this node. Even better, you may have seen that there is a button to “Guess data type and format”.

Having said this, sometimes even that doesn’t work. So, would you mind please to upload here a workflow or a file with the column that causes problem ? Thank and looking forward to helping.

Best

Ael

[Karlygash]

Hi, @aworker thanks
How are you?)

Yeah, I have tried to use guess data format, but it did not offer me proper format=)
Uploaded my simple workflow in here
date format.knwf (12.5 KB)

Thanks in advance

[bruno29a]

Hi @Karlygash , to add to what @aworker said, can you also send the error message? It usually would tell you what record did not comply to the format.

[Karlygash]

Hi @bruno29a

Thanks for your reply
Of course,

[aworker]

Hi @Karlygash

The month needs be Capitalize three letters. In your case, months are 3 Uppercase letters:

Which doesn’t help here

You would need first to build a -look up table- to replace the 3-letters-months by for instance their 2-digits-number, for instance, and then you will be able to convert the dates.

Hope this helps

Best

Ael

[bruno29a]

Hi @Karlygash , the issue here is that the data that you have is not a valid format recognized by Knime.

The format that comes close to it is whatever you have, which is “dd-MMM-yy”, but this format supports this “13-Jan-01” instead of “13-JAN-01”. Basically it’s the uppercase values of the month that need to be changed, and it will work after that.

I did the manipulation on the data to change the case, and it works:

After manipulation:

And the results (converted to dates):

Here’s the workflow: date format.knwf (18.7 KB)

[Karlygash]

Thanks

[aworker]

The worklow is in its way, hold the line please …

[Karlygash]

ok

[bruno29a]

@aworker , @Karlygash , there is no need to map the months to their corresponding digits. MMM format can recognize Jan, Feb, etc…

[aworker]

20210710 Pikairos Mess with datetime format.knwf (35.8 KB)

Enjoy your weekend @Karlygash !

Best

Ael

[Karlygash]

@aworker , @bruno29a thank you

As usually, helped me to solve this problem
@aworker , Lookup table really useful when I have other mess of date format
@bruno29a thanks to your solution as well

[aworker]

Hi @bruno29a

My apologies, you are right and I have modified my previous answer so that it doesn’t mislead in future other readers. Your solution is much simpler and hence the best.

Please @Karlygash validate @bruno29a’s answer as the solution. It will help people to find the best one in this case. I’m still leaving mine so that people may have a second option as stated by @Karlygash.

As the saying says: “Take your time if you are in a hurry! ”

Thanks @bruno29a & @Karlygash

Best

Ael

[Karlygash]

@aworker thank you)

Have a very enjoyable weekend

[bruno29a]

Hey @aworker , there’s nothing to apologize for

I just wanted to make sure that users know that MMM supports Jan, Feb, etc, and that in this case, given the data, it made sense to use MMM.

As we always say, there are many ways to do things in Knime, and what you suggested is also a way to do it. Moreover, it also provides an alternative, should other users have other date formats, for example 01-January-19, where they can adapt your solution and map January, February, etc… to either Jan, Feb, etc or to 01, 02, etc… and use MMM or mm.

All good

[aworker]

Good to know, uff !

I feel relieve and I can now sleep as a log haha ha (et même dormir sur mes deux oreilles )

Thanks @bruno29a !

@bruno29a & @Karlygash have a nice weekend,

Ael

[bruno29a]

@aworker

et même dormir sur mes deux oreilles

En même temps, ou un côté à la fois?

[aworker]

Haha ha

Bon weekend Bruno !

Ael

[takbb]

Looks like I missed out on all the fun!

This was my take a couple of months ago (just for a laugh!) on something that could handle a wide variety of date formats provided that the data was presented in a known order. Obviously it is locale dependant and doesn’t internationalise well in its current form, but who knows, maybe one day I’ll try turning this into an actual node

KNIME_Workflow With flexible DATE format mask – takbb

It successfully converts all the dates in the annotation on the left (and more besides!)

It also includes a US variant:

Well… I needed something to do