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.NOTE: If the source Datetime value does not include a valid input for this function, a missing value is returned.
There are differences in how the WEEKNUM function is calculated in Photon and Spark running environments, due to the underlying frameworks on which the environments are created:
- Photon week 1 of the year: The week that contains January 1.
- Spark week 1 of the year: The week that contains at least four days in the specified year.
Basic Usage
Column reference example:
derive type:single value:WEEKNUM(MyDate)
Output: Generates a column of values containing the numeric week number values derived from the MyDate
column.
Syntax and Arguments
derive type:single value:WEEKNUM(datetime_col)
Argument | Required? | Data Type | Description |
---|---|---|---|
datetime_col | Y | datetime | Name of column whose week number values are to be computed |
For more information on syntax standards, see Language Documentation Syntax Notes.
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))
.
Usage Notes:
Required? | Data Type | Example Value |
---|---|---|
Yes | Datetime | myDate |
Tip: For additional examples, see Common Tasks.
Examples
Example - Day of Date functions
WEEKDAY
- returns numeric value for the day of the week for source Datetime values. See WEEKDAY Function.WEEKNUM
- returns the numeric value for the week within the year for source Datetime values. See WEEKNUM Function.DATEFORMAT
- can be used to format Datetime values in many different ways. See DATEFORMAT Function.
Source:
myDate |
---|
10/30/17 |
10/31/17 |
11/1/17 |
11/2/17 |
11/3/17 |
11/4/17 |
11/5/17 |
11/6/17 |
Transform:
The following generates a numeric value for the day of week in a new column:
derive type:single value: WEEKDAY (myDate) as:'weekDayNum'
The following generates a full text version of the name of the day of the week:
derive type:single value:DATEFORMAT(myDate, 'EEEE') as:'weekDayNameFull'
The following generates a three-letter abbreviation for the name of the day of the week:
derive type:single value:DATEFORMAT(myDate, 'EEE') as:'weekDayNameShort'
The following generates the numeric value of the week within the year:
derive type:single value: WEEKNUM (myDate) as:'weekNum'
Results:
myDate | weekDayNum | weekDayNameFull | weekDayNameShort | weekNum |
---|---|---|---|---|
10/30/17 | 1 | Monday | Mon | 44 |
10/31/17 | 2 | Tuesday | Tue | 44 |
11/1/17 | 3 | Wednesday | Wed | 44 |
11/2/17 | 4 | Thursday | Thu | 44 |
11/3/17 | 5 | Friday | Fri | 44 |
11/4/17 | 6 | Saturday | Sat | 44 |
11/5/17 | 7 | Sunday | Sun | 45 |
11/6/17 | 1 | Monday | Mon | 45 |
This page has no comments.