string to date messy issue

Hi All,

I am an reasonable level KNIME user (but by no means anywhere near a programmer) and i am having a real pain trying to convert a string from excel into what should be a date. The string is appearing as:

Feb 4 2021 12:00AM
Feb 22 2021 12:00AM

I have tried so many different options using ‘string to date&time’, ‘String to date/time (legacy)’ and cant seem to get this to work as even specifying my own date format, i consistently get the ‘No suitable format found!’ message. I even used string manipulation to try and help simplify and remove time so i only have Feb 4 2021 or Feb 22 2021.

I would really appreciate anyone’s expertise here as it is driving me bonkers and is the last step before i cant create the remaining workflow for my output - which will be relatively quick.

Hi @JanHazel

If you overrtype the “Date format” field wiith MMM d yyyy h:ma
and make sure the “New type” field shows “Date&time” this should work. To be honest I’ve seen the “No suitable format found” message before quite a few times, and I’ve kind of ignored it in favour of overtyping the format string. (I’m guessing it continued to appear because the format you’d tried wasn’t quite right maybe)

image

image

Hope that helps

3 Likes

Hi @takbb
if i’m not completely on the wrong path the timestamp 12:00AM should result in ‘2021-02-04T12:00’ and not as shown ‘2021-02-04T00:00’.

BR

Hi @morpheus you would think so wouldn’t you, but no it’s a strange quirk of am/pm vs 24 hour but 12am is actually midnight, and 12pm is midday, or noon.

It feels a little illogical in my view too but this is just the way it works.

00:00 to 00:59 == 12.00am to 12:59am
01:00 to 11:59 == 1.00am to 11.59am
12:00 to 12:59 == 12.00pm to 12.59pm
13:00 to 23:59 == 1.00pm to 11.59pm
When layed out you can see that everything from midnight through to just before noon is considered morning (am) and everything from noon until just before midnight is considered afternoon (pm).

And even more fun… for a party conversation… I believe technically there is no such thing really as 12pm, since pm translates to “post-meridian” (ie, after-noon) so the time really runs as 11:59 am, noon, 12.01pm… :joy: but I think we can ignore that!

Odd but true! :slightly_smiling_face:
br
Brian.

5 Likes

Hi Brian,

thank you for the detailed explanation. One reasen more to be lucky living in a country having 24 hours a day. :grin:

BR
Hermann

3 Likes

You are right! But I can tell you that there are many people here in the UK who live with this system and would also think that 12pm is midnight! I too prefer to use 24 hour clock where possible :slight_smile:

When I was learning German/Deutsche at school, there was often confusion between spoken times. In the UK, 10:30 might be spoken as “half PAST ten”, or more simply “half ten”, but as I recall in German, it would translate as “half eleven” (Half BEFORE eleven) :slight_smile: So yes 24 hour clock is the way to avoid missing the train when in another country!

1 Like

Hi Brian,

Thanks so much for your help. Funnily enough after posting i kept perservering with this as i had been overtyping as you suggested, and I eventually got it with MMM dd yyyy kk:mma. Appreciate your reply immensely though and just reinforces was along the right track.

Appreciate the KNIME community being so helpful, onto the next challenge now!

2 Likes

Hi @JanHazel, glad you got it working. I’m surprised your mask of MMM dd yyyy kk:mma works for you though, as I thought dd required two-digit days of the month, so would fail for Feb 4 2021… as it would be expecting Feb 04 2021… whereas just a single d will match both. Likewise the kk part is normally for matching hours in 24 hour (01 to 24) format whereas h will match hours in (1-12) format for use with am/pm. But maybe that is how your dates actually are.

Anyway, like I said, glad you got it working as date/time masks can be fiddly being based on the rather counter-intuitive java masks, and I find that I’m often having to look them up again if I haven’t needed to use them for a while.

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.