How to get date of start of the week based on date column

sljw · June 19, 2020, 1:50am

Hi,

I’m pretty new to Knime but I would like to find out how I can calculate the week commencing date based on a column of dates.

For example if my date is 19/6/2020 then the week commencing date (Mon-Sat) should be 15/6/2020.

Thanks!

webstar · June 19, 2020, 5:33am

What happens is the date is June 14 Sunday?
Do you want the previous Mon June 8th - Sat June 13 ?
or do you want the Mon-Sat which comes after which is June 15-June 19th?

andrejz · June 19, 2020, 5:47am

Hi,

Try with the “Date&Time Extraction” node.
From the date you can extraxt week number and then with “Groupby” node group by week and manual aggregation select minimum of the date column.
Or you can extract day name an then filter mondays (or sundays)

webstar · June 19, 2020, 5:57am

Agree with above - I used the following seq. to get at your problem

create a date and time range fixed for 1 day
extract date and time fields to get day of week number and day of week name (really just need number)
Rule engine
$Day of week (number)$ =1 => 1
$Day of week (number)$ =2 => 0
$Day of week (number)$ =3 => -1
$Day of week (number)$ =4 => -2
$Day of week (number)$ =5 => -3
$Day of week (number)$ =6 => -4
$Day of week (number)$ =7 => -5
appending a new column called Start_index
date& time shift: select numerical column start_index with Granularity days
5) A second date time shift node with fixed number of days as 5
You always get Monday to Sat no matter what you put in . with Sunday you get the immediate Mon - Sat.