Mess with datetime format

Hello, dear Knimers=)

Can you help me with this date format


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

1 Like

Hi @Karlygash

Welcome back :grinning: !

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

3 Likes

Hi, @aworker thanks :smile:
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 :green_heart:

1 Like

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.

3 Likes

Hi @bruno29a

Thanks for your reply
Of course,

Hi @Karlygash

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

Which doesn’t help here :pensive:

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 :wink:

Best

Ael

2 Likes

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:
image

After manipulation:
image

And the results (converted to dates):
image

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

5 Likes

Thanks :star_struck:

The worklow is in its way, hold the line please :rofl:

1 Like

ok :laughing: :innocent:

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

2 Likes

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

Enjoy your weekend @Karlygash !

Best

Ael

3 Likes

@aworker , @bruno29a thank you :green_heart:

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

2 Likes

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! :wink:

Thanks @bruno29a & @Karlygash

Best

Ael

2 Likes

@aworker thank you)

Have a very enjoyable weekend :hugs:

1 Like

Hey @aworker , there’s nothing to apologize for :slight_smile:

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 :+1:

2 Likes

Good to know, uff :relieved: !

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

Thanks @bruno29a !

@bruno29a & @Karlygash have a nice weekend,

Ael

1 Like

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

1 Like

Haha ha :smile:

Bon weekend Bruno !

Ael

1 Like

Looks like I missed out on all the fun! :wink:

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 :slight_smile:

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 :wink:

4 Likes