Overview
The Data Transformation module includes a built-in Date Component step that can extract specific date elements from Date or Timestamp fields. For example, if all you require from your date data is the exact day of the week, or if you need to convert the values to the last date of a given month, then this step will allow you to easily perform such functions. See the table below for a complete list of all date and time functions.
Note: This is a built-in step, accessible via the Transformation Step panel.
Date Step Functions
This step offers various date/time functions related to the different types of date components.
| Date Component | Function | Description | Example (Original format: dd-mm-yyyy) |
|---|---|---|---|
Year | Year | Returns the year of the date. Note: The data type of the output field will be changed to Date if it was Timestamp. | Original data: 21-05-2017 Output: 2017 |
Year Start Date | Converts the date values to the first date of the year (that is, Jan 1st). Output field data type: Date | Original data: 21-05-2017 Output: 01-01-2017 | |
Year End Date | Converts the date values to the last date of the year (that is, Dec 31st). Output field data type: Date | Original data: 21-05-2017 Output: 31-12-2017 | |
Quarter | Quarter | Displays the quarter of the date field in numeric form. Output field data type: Numeric | Original data: 21-04-2017 Output: 2 |
Quarter - Ordinal | Returns the quarter rank of the date field as an ordinal number. Output field data type: Text | Original data: 21-04-2017 Output: 2nd | |
Quarter - Prefixed | Returns the quarter rank of the date field. Output field data type: Text | Original data: 21-04-2017 Output: Q2 | |
Month
| Month Name | Returns the month name of the given date. Output field data type: Text | Original data: 21-04-2017 Output: April |
Month Name - Short | Returns the abbreviated form of the month. Output field data type: Text. | Original data: 21-04-2017 Output: Apr | |
Month Number | Returns the number of the specific date’s month. Output field data type: Numeric. | Original data: 21-04-2017 Output: 4 | |
Month Number - Ordinal | Returns the rank of the date month as an ordinal number. | Original date: 21-04-2017 Output: 4th | |
Month Start Date | Converts the date values to the first date of the month (while retaining the original month and year values). Output field data type: Date | Original date: 21-04-2017 Output: 01-04-2017 | |
Month End Date | Converts the date to the last date of the month. Output field data type: Date | Original date: 06-02-2018 Output: 28-02-2018 | |
Week | Week of Year | Returns the number of the week of the year. Output field data type: Numeric | Original date: 21-11-2017 Output: 47 |
Week of Month | Returns the number of the week of the month. Output field data type: Numeric | Original date: 21-11-2017 Output: 4 | |
Week Start Date | Changes the date to the start date of the week. This function will determine the week of the given date, and change the date to the first day of that week, assuming that the week starts on a Sunday.) Output field data type: Date | Original date: 30-11-2017 Output: 26-11-2017 | |
Week End Date | Changes the date to the end date of the week. This function will determine the week of the given date, and change the date to the last day of that week, assuming that the week ends on a Saturday.) Output field data type: Date | Original date: 30-11-2017 Output: 02-12-2017 | |
Day | Day Name | Returns the full name of the day of a given date. Output field data type: Text | Original date: 30-11-2017 Output: Thursday |
Day Name - Short | Returns the shorter version of the day name of a given date. Output field data type: Text | Original date: 30-11-2017 Output: Thu | |
Day of Week | This determines the day of the week of a given date and returns its number. Output field data type: Numeric | Original date: 30-11-2017 Output: 5 | |
Day of Week - Ordinal | This determines the day of the week of a given date and returns ordinal number. Output field data type: Text | Original date: 30-11-2017 Output: 5th | |
Day of Month | This determines the day of the month of a given date and returns it’s number. Output field data type: Numeric | Original date: 11-04-2017 Output: 11 | |
Day of Year | This determines the day of the year of a given date and returns its number. Output field data type: Numeric | Original date: 11-04-2017 Output: 101 | |
Time (Only appears if your data has a timestamp) | Hour (AM/PM) | Returns the hour in the form of the 12-hour clock (that is with AM or PM). Output field data type: Text | Original value: 11-04-2018 06:37:00:17 Output: 6 AM |
Hour | Returns only the hour of the given timestamp. Output field data type: Numeric | Original value: 11-04-2018 06:37:00.008 Output: 6 | |
Minute | Returns the minute of the given timestamp. Output field data type: Numeric | Original value: 11-04-2018 06:37:00.008 Output: 37 | |
Second | Returns the second of the given timestamp. Output field data type: Second | Original value: 11-04-2018 06:37:00.008 Output: 0 | |
Millisecond | Returns the millisecond of the given timestamp. Output field data type: | Original value: 11-04-2018 06:37:00.008 Output: 8 |
Step Configuration
Follow the instructions below to configure a date component step, and extract date elements:
- Your flow must include a Date or Timestamp field on which to perform a date or time function. Once you are ready to use the Date Component step in your flow, proceed with the instructions below:
- Expand the Transformation Steps panel on the left side of the Transformation Flow builder, and drag the Date Component step onto the canvas.
- Connect this step to the previous step in the flow. On doing so, its configuration details will appear on the right side panel.
- From the Date Field drop down list, select the date column field that you wish to apply a date function on.
- From the Date Function drop down, choose a date component. On doing so, the Format drop down will be updated to show all the functions related to this component.
- Select the exact function that you wish to perform on your date data from the updated Format drop down. See the table above for a list of all available functions.
- Click Apply. A new column with the result of this step will appear in the data preview panel. (Note: The name and data type of the output column will be based on the function applied.) The example below shows the result of a millisecond extraction on a timestamp.
