im having some strange trouble with merging two columns. I have one column with a date (dd/mm/yyyy) and one with a time (e.g 19:00) i need these in one column dd/mm/yyyyT19:00 but when i merged them in excel using a merging formular KNIME will read the format as a string, and it doesn’t work with a string to date&time to set it as a date.
If I instead try and keep the 2 columns in excel and wanna merge them i KNIME, the excel reader node reads my time column as a date and time and automatically adds the date 1899-12-31 to all rows even though the time is set as time with you can choose what format it is
does anyone know of this problem or have an idea for a solution
Yes the problem is that I merged the cells in excel using =TEKST(A1;"dd/mm/iiii ") &TEKST(B1;“tt.mm”) and it seems that KNIME are not able to read it as a date at all
haha good idea but still nothing. it looks right in the output but when i try to e.g. calculate time difference with a Date&TimeDifference in turns up with a minuts count that is way off for 1 day
(Visit date) is the string column that needs to be converted.
after that, i tried to calculate the difference between visit day and booking day… but it seem like it perhaps draws on the “time” column which as earlier mentioned turns time into time and the year 1899. and further more is the column that together with date created “visit date” - made in excel KNIME_datetime.knar.knwf (9.4 KB)
Your first cell contents from your screenshot is 2011-01-01;00:00:00.0
so are all your times set at 00:00:00.0 ? and is there always a semi-colon separating your date and time components? If there is, the mask needs to reflect that.
On the face of it, it looks like the format mask of
yyyy-MM-dd;HH:mm:ss.S
would work but I have a couple of questions
If your months and days are always two digits, that should work. But if they can both be single digit or two digit, then you’d need to use just single M and d as this allows both:
yyyy-M-d;HH:mm:ss.S
Do the number of decimal places after the seconds vary? If it could be one, two or three decimal places, you’d need to include optional (alternate) masks.
e.g.
yyyy-MM-dd;HH:mm:ss.[SSS][SS][S]
This would allow for three, two, one decimal places.
Hope that helps. If not, upload a representative sample of your dates and we can take a look.
I am sorry, I updated the screenshot as I had uploaded the wrong one. Months and days are always 2 digits. I have the measures every hour and I only converted in this way the hours because this was the format accepted.
The sample is working but when I upload all the dataset I am trying to convert the date time I receive this error
, you are totally right, now everything is ok 