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.
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'
derive type:single value:DATEFORMAT(myDate, 'EEEE') as:'weekDayNameFull'
derive type:single value:DATEFORMAT(myDate, 'EEE') as:'weekDayNameShort'
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.