Skip to main content

WEEKNUM Function

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.

Anmerkung

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

Wrangle vs. SQL: This function is part of Wrangle, a proprietary data transformation language. Wrangle is not SQL. For more information, see Wrangle Language.

Anmerkung

Depending on the locale where the calculation of this function is performed, the maximum number of weeks in a year may be 52 or 53. It's possible that you could see different results in your browser, Trifacta Photon, and other running environments due to locale.

Basic Usage

Column reference example:

weeknum(MyDate)

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

Syntax and Arguments

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.

Tipp

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

Examples

Tipp

For additional examples, see Common Tasks.

Example - Day of Date functions

This example illustrates how you can apply functions to derive day-of-week values out of a column of Datetime type.

Functions:

Item

Description

WEEKDAY Function

Derives the numeric value for the day of the week (1, 2, etc.). Input must be a reference to a column containing Datetime values.

WEEKNUM Function

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.

DATEFORMAT Function

Formats a specified Datetime set of values according to the specified date format. Source values can be a reference to a column containing Datetime values.

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

Transformation:

The following transformation step generates a numeric value for the day of week in a new column:

Transformation Name

New formula

Parameter: Formula type

Single row formula

Parameter: Formula

WEEKDAY (myDate)

Parameter: New column name

'weekDayNum'

The following step generates a full text version of the name of the day of the week:

Transformation Name

New formula

Parameter: Formula type

Single row formula

Parameter: Formula

DATEFORMAT(myDate, 'EEEE')

Parameter: New column name

'weekDayNameFull'

The following step generates a three-letter abbreviation for the name of the day of the week:

Transformation Name

New formula

Parameter: Formula type

Single row formula

Parameter: Formula

DATEFORMAT(myDate, 'EEE')

Parameter: New column name

'weekDayNameShort'

The following step generates the numeric value of the week within the year:

Transformation Name

New formula

Parameter: Formula type

Single row formula

Parameter: Formula

WEEKNUM (myDate)

Parameter: New column name

'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