TimeDate extraction fro

KNIME Analytics Platform
1

I have a column of ‘string’ type which has data in ‘2019-09-13T07:54:34.0000000Z’, I want to extract date, time , day of the week and end of the week(date).
options I have tried:
1)I have tried using ‘extract date & Time Fields’ Node , but getting error No column in spec compatible to “Local date vale”“LocalTimeValue"or"ZoneddateTimeValue”
2) I have tried using string to column expression to removing T,Z and then using ‘string to date&Time’ node but then getting error-Text ‘2019-09-1307:54:34.0000000’ could not be parsed, unparsed text found at index 10
Can you help me to get this resolved.

2

Hi @analytics_sharma,
using the Column Expression is not necessary. Just use the String to Date&Time node with the following date format:

yyyy-MM-dd'T'HH:mm[:ss[.SSSSSSS]]'Z'

Kind regards
Alexander

3 Likes
3

Thanks Alexander , I am able to fetch date and time , Day of the week(Extract Date&Time Fields) but how can I fetch end date of the of the week from ‘2019-09-1307:54:34.0000000’?

4

Hi,
by “end date of the week”, what do you mean exactly? The next Friday, Saturday, Sunday? Or the week within the year, i.e. rounding to the next day of the year divisible by 7?
Kind regards,
Alexander

5

By end date of the week, I man-next Saturday Date.

6

Hi @analytics_sharma,
for that you can first extract the date of week using the Extract Date&Time Fields node. Then use a Math Formula node with the expression if($Day of week (number)$ <= 6, 6 - $Day of week (number)$, 6) to calculate the number of days to add and feed that to a Date&Time Shift node, setting the “Use numerical” option and choosing the column that was calculated by the math formula.
Kind regards
Alexander

1 Like
7

Thanks , it is the solution I am looking for, instaed of 6 I have used 7 in the expression, since the week starts from Sunday.
Once again,Thanks a lot Alexander:)

8

One issue we are facing, for few records in the input table , we have null values in the ’ date column’ because of which the whole execution of node ‘String to Date&Time’ is failing and we cannot ignore these records.
How can this can be handled.?

9

Hi,
you can filter out the rows containing missing values with a Row Splitter, then do the String to Date&Time only on the partition with values. In the table with missing values you can remove the column and then you can merge the tables again, e.g. with a Concatenate node.
Kind regards
Alexander

1 Like
10

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