Hello i have a csv file in which there are timestamps in this format:
“2021-06-09 10:00:00”
Which in the End i want to have as “dd.MM.yyyy HH:mm:ss”
I didn’t get this done with the string to Date&Time node, have i overlooked something? If not what is the best way to do this?
Ready to play:
Greetings!
Hello @TotalDataLoss,
the following should do it. String to Date&Time (yyyy-MM-dd HH:mm:ss) followed by Date&Time to String (dd.MM.yyyy HH:mm:ss).
Br,
Ivan
Hi ipazin,
i can’t chose yyy-MM-dd HH:mm:ss it doesn’t exist (i am using version 4.3.2)
the closest i can chose is:
“yyyy-MM-dd HH:mm:ss.S”
If i run it it gives the error message:
ERROR String to Date&Time 0:2 Execute failed: Failed to parse date in row 'Row0: Text ‘2021-06-09 10:00:00’ could not be parsed at index 19
Hello @TotalDataLoss,
you can write your own format and edit existing. Drop-down menu is here only to offer most used formats and to remember ones you already used 
Why are so many users struggling with this? 
(Actually the question is how to improve it/make it more intuitive)
Br,
Ivan
Hi ipazin,
thanks that did it!! But without your help i would have thought its not possible. i played with it a bit to find out why i wasn’t able to do it and since you asked:
Well this played a role how i came to the conclusion that this is not possible:
First I clicked on “Guess data type and Format” it then writes the “YYYY” as capitals. And it switches under “New Type” to “Time” (from Date&Time) since i Wanted Date&Time i switched it back to “Date&Time”.but now if you run it it gives you an error message. At the point i did even try to change the format by typing, but i already had the wrong idea in my head that the y’s have to be capital Y’s.
On a sidenote: What adds to this is that i intuitively thought that the letters are case sensitive and have a “theirr own logic” like “HH” needs to be capital but “mm” is small so i figurerd "stay with what guess gives you so "YYYY "needs to be capital…
But anyways: Thank you a lot!! it now runs “better than ever” since i now understand it better: 
Improve the “Auto-Guess” Option 
I guess that is not trivial so people have to rely on this great community
Maybe that’s possible as well @Daniel_Weikert 
Tnx for explanation @TotalDataLoss!
Hopefully someone will come up will cool way to address this annoyance 
Ivan
Hi @TotalDataLoss, you certainly aren’t alone in finding problems with the date time formatting. The date formats come from Java’s (poorly named imho “SimpleDateFormat” class, which contains illogical inconsistencies and poor design decisions (as I said, IMHO )
The format letters are of course in the help documentation for the node, but it doesn’t help with the subtlety of “how” it works.
For example as you have already pointed out capital M is Month but lower case m is minute. Ok, I can live with that bit as they needed the letter “m” for both, and had to make a distinction, but then we have that MM is a two-and-only-two digit month, whereas M means “either one or two digits”, but MMM means Month Name, except that the string it interepts is case-sensitive so in the UK it cannot understand “jan” or “JAN”, but only “Jan” (a ridiculous design decision for what should be a “simple” date format, and worse than that, they had to make it case-sensitive based on the locale, so we cannot easily standardise on just throwing something at it that contains the correct letters in the correct order. In the UK it’s Title case, but in Italy its lower case - because in Italy months are not written with any capitals, whereas in the UK generally they are)
The we look at the mm vs m situation. So mm is a forced-two-digit minute, whereas m allows one or two digit minutes… s and ss presumably do the same for seconds, and then we get to Milliseconds - described in the node help as “fraction of a second” - (where the designers ran out of variations on the letter M, so used capital S) and then people naturally assume that when it comes to milliseconds, that maybe .S should cover it. “s” covers one or two digit seconds, so why shouldn’t S cover all the variations of one, two or three digit Milliseconds?.. but no! S means exactly one digit (tenths), SS is exactly two digits (hundredths), and SSS is exactly three digits (thousandths/millseconds), so to cover the bases for all variations (down to) Milliseconds, we have to put in [[.SSS][.SS][.S]] and that isn’t going to be obvious to anybody until they hit that particular wall!
Then we have Year, where we can choose, two or four digit years with yy and yyyy, and what happens when we have dates such as:
01-02-03
2001-02-03
representing 3 Feb 2001 ?
We try our date format yy-M-d and it chokes with 2001.
We try our date format yyyy-M-d and it chokes 01 .
Then we scratch our heads and maybe think how clever the designers were in allowing us just to put in y-M-d… And that’s what we try, and it doesn’t shout at us that it’s wrong, so we get on with our lives, not realising that “y” handles both 2 and 4 digit years but not in the way that we think it will. It doesn’t use the generally recognised standard for dealing with two-digit dates, (i.e. based on a cut-off year go with either 19xx or 20xx)…oh no… instead it interprets 01-02-03 as 0001-02-03 
So for year, if we want to be sure to cover both 4 and 2 digit, we end up with something like [yyyy][yy]-M-d which leaves some room for errors in the data!
Well that’s got that off my chest. 
No…I really can’t understand why anybody has problems with date and time formats… 
@takbb: Ja Thanks for this longer explanation. That’s a lot how i felt with this feature - i could have written much more - a lot of confusing stuff going on and often this gets one on the wrong track so you “learn” false things. have a good one!
I’m not even going to add to this 
Been dealing with date format for at least 20 years, I am able to adapt to any system now , but though they’re not all the same (capital y vs lowercase y, or m, etc), they mostly follow the same concept. The only thing new for me (well, it’s been about 5 years now) was the inclusion of milliseconds.
I would still think that you should need that much experience to figure it out. We see documents (hard copy) where you have to sign and date that use these formats to indicate how you should date the document. And you can always play around with the formats to see what they return.
So much for me not going to add to this lol
This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.