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
The string to date&time format that is not working is “dd/MM/yyyyTHH:mm” ??
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
The content of the first cell does not appear to match your selected date format.
I would try this as the format “dd/MM/yyyy HH:mm”
still the data won’t read with that format
Sorry, my bad, old eyes cannot spot the difference between . and :
Change the : in the format to . and try again.
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 workflow does not contain data. Uncheck the reset option when exporting the workflow or upload the data file as well.
@Alberte I have the same issue here. Did you solve it?
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
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:
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.
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.
@takbb Thank you for your prompt response.
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
I checked the row it seems to be ok. What may be wrong here?
It looks from the message like that line has a single digit second. So try this:
If some records have ; and others do not, then you could also use:
Hello @Alberte ,
you have to write your date/time format in the exact way. The problem ist “.” between 19 and 15.
Type in dd/MM/yyy HH.mm and it should work
@takbb thank you again , you are totally right, now everything is ok