EXAMPLE - Day of 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 ( |
WEEKNUM Function | Derives the numeric value for the week within the year ( |
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 |
|
|---|---|
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 |
|
|---|---|
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 |
|
|---|---|
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 |
|
|---|---|
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 |