weekEnd

weekEnd(X, [weekday=6], [offset], [n=1])

Alias: week

Calculates the end date of the period containing the specified date(s) in X. By default, the period length is one week, and the period-ending day is specified by the weekday parameter. The default value of weekday is 6 (Sunday).

When both offset and n are specified (with n > 1), the function calculates period end dates based on n-week periods. In this case, offset determines how period boundaries are aligned. Specifically, the system first calculates the end date of the period containing offset according to weekday. This date is then used as the reference point, and a new period-ending boundary is generated every n weeks. The function returns the end date of the period to which X belongs.

X is a scalar/vector of type DATE, DATETIME, DATEHOUR, TIMESTAMP or NANOTIMESTAMP, specifying the input date(s) or datetime(s) for which the period end date is to be calculated.

weekday (optional) is an integer from 0 to 6 that specifies the week-ending day. 0 means Monday, 1 means Tuesday, ..., and 6 means Sunday. The default value is 6.

offset (optional) is a scalar of the same data type as X. It must be no greater than the minimum value of X.

When n > 1, offset is used to determine the alignment reference for multi-week periods. If offset is not specified, the minimum value in X is used as the default offset.

n (optional) is a positive integer that specifies the number of weeks in each period. The default value is 1. When n = 1, the function calculates period end dates based on calendar weeks and offset has no effect. When n > 1, the function calculates period end dates based on n-week periods, in which case both offset and n must be specified.

A scalar or vector of the DATE type.

Example 1

t = table(2017.12.01..2017.12.14 as date)
update t set weekday=weekday(date, false), weekEnd=weekEnd(date), weekEnd4=weekEnd(date,4)
t;

Example 2

t = table(2018.01.03+0..10*3 as date, 0..10 as x)
update t set weekday=weekday(date, false), weekEnd2=weekEnd(date,,2018.01.02,2)
t;

Example 3

date=2012.10.02 2012.10.03 2012.10.07 2012.10.08 2012.10.12 2012.10.16 2012.10.18 2012.10.20 2012.10.25 2012.10.28
time=[09:34:07,09:36:42,09:36:51,09:36:59,09:32:47,09:35:26,09:34:16,09:34:26,09:38:12,09:38:13]
sym = take(`MSFT,10)
price= 49.6 29.46 29.52 30.02 174.97 175.23 50.76 50.32 51.29 52.38
qty = 2200 1900 2100 3200 6800 5400 1300 2500 8800 4500
t1 = table(date, time, sym, qty, price);

t1;

select avg(price),sum(qty) from t1 group by weekEnd(date, 4, 2012.10.01, 2);

Related function: weekBegin