View Source

Derives the numeric value for the week within the year (1, 2, etc.). Input must be the output of the DATE function or a reference to a column containing Datetime values. The output of this function increments on Sunday.

Week 1 of the year is the week that contains January 1.

NOTE: If the source Datetime value does not include a valid input for this function, a missing value is returned.

Known Issue:

The current implementation of the WEEKNUM function returns a maximum value of 52. However, in some years, such as 2020, there is a 53rd week. You can add steps similar to the following to work around this issue:

derive type: single value: dateformat(date(year(myDate), 1, 1), 'yyyy\/MM\/dd') as: 'NewYearsDayforMyYear'

derive type: single value: if(datedif(NewYearsDayforMyYear, myDate, day) > (52 * 7), 53, weeknum(myDate)) as: 'weekNumforMyDate'

The above implementation differs from other platforms, such as Microsoft Excel.

Column reference example:

weeknum(MyDate)

Output: Returns the numeric week number values derived from the MyDate column.

weeknum(datetime_col)

Argument	Required?	Data Type	Description
datetime_col	Y	datetime	Name of column whose week number values are to be computed

datetime_col

Name of the column whose week number value is to be computed.

Missing values for this function in the source data result in missing values in the output.
Multiple columns and wildcards are not supported.

Tip: You cannot insert constant Datetime values as inputs to this function. However, you can use the following: WEEKNUM(DATE(2017,12,20)) .

Required?	Data Type	Example Value
Yes	Datetime	`myDate`

datetime_col

Example - Day of Date functions