Unable to find the right format for String to date&time

Hey there,

I want to extract the date and first I have to use the node String to Date&Time, but I have trouble finding the right date format.

Appreciated for the help!

Best Regards

Hi @ppeng , based on the content of your first cell, which is 1/31/2018 22:56 the correct format would be: M/dd/yyyy HH:mm or M/d/yyyy HH:mm (31 does not show how single digit days are - you should share what the content of the field is)

4 Likes

Hi @bruno29a,

thanks for helping out!

here is the column content. What I would like to try is to turn it to date and extract it later on (into years, months, quarter and weeks) but cant find the right format

Formatting is ok . Change the new type to Datetime

1 Like

Hi @ppeng , so your screenshot is not telling the whole story, although it did partially. We can see the format to use for the time part.

I will explain to you how the format works so that you can understand it and eventually find the correct format to use. Basically, the tricky part relates to the day, month and hour, where the values less than 10, are represented in a single digit or double digits (1, 2, 3, etc… vs 01, 02, 03, etc…).

We can see for the month and the hour, they are in single digit, but the sample that you have do not have values less than 10 for the day, so I can’t know if the values are represented in single or double digits. Based on the fact that the month and hour are in single digit, it is most likely that the day is also in single digit.

You can refer to this table to understand what format to use:

Single digit Double digits
Day d dd
Month M MM
Hour H HH

So, in your case, it will probably be: M/d/yyyy H:mm, or you can check what the whole data looks like and apply the proper format based on what I explained.

Note: Similarly, the same thing applies to the minute values with m for single digit and mm for double digits, but I almost never see minutes in single digit representation (never saw 10:7, always 10:07). Same for seconds

Once you set the proper format, you will get a column of type datetime. After that, if you want to extract the different components of the datetime (years, months, etc), you can use the Extract Date&Time Fields node on that column, and you can choose what you want to extract.

3 Likes

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