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.
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 |
Follow the instructions below to configure a date component step, and extract date elements: