Description:

The Transform Datetime by Extracting a Part step extracts one or more components from a date/time value and makes them available for downstream ETL transformations.

The transformation is configured through the Configure Transform List property, where ETL designers specify the source date/time column together with the date or time component that should be extracted. Depending on the configured transformation, extracted values may include the year, quarter, month, week, day, day of week, hour, minute, second, or other supported date/time parts.

Unlike formatting transformations, which change only the appearance of a timestamp, or time adjustment transformations, which modify the underlying value, this step isolates individual date/time components for use in reporting, grouping, filtering, calculations, or business intelligence.

The Transform Datetime by Extracting a Part step is commonly used after validating source data and before performing grouping, aggregation, filtering, or report generation.

The step supports:

• Date component extraction
• Time component extraction
• Multiple extraction rules
• Reporting preparation
• Business intelligence processing
• Enterprise ETL workflows
• Integration with FlowWright ETL pipelines

This step can be used for:

• Extracting reporting periods
• Building monthly or yearly summaries
• Grouping records by date parts
• Business analytics
• Dashboard preparation
• Data warehouse loading
• Enterprise reporting

Usage:

The Transform Datetime by Extracting a Part step is typically inserted after importing and validating date/time values, but before grouping, filtering, aggregation, or reporting operations.

During execution, each configured rule extracts the specified component from the source date/time value. The extracted values become available for downstream ETL components and may be used for grouping, sorting, filtering, reporting, or exporting.

A typical ETL pipeline might look like this:

Typical extraction scenarios include:

• Extracting the year for annual reporting
• Grouping transactions by month
• Creating quarterly sales summaries
• Filtering records by day of the week
• Building hourly operational dashboards
• Preparing fiscal reporting periods
• Loading dimensional attributes into a data warehouse

The extracted values simplify downstream analytical processing by exposing individual date and time components as separate fields.

Prerequisite:

The ETL process engine service should be running to execute the ETL definitions.

Example:

Let’s build and execute the “clsTransformExtractTimepartDef” example.          

• Create a new ETL definition named “clsTransformExtractTimepartDef” and open it in designer mode. 
• Drag the “clsInputCSV, clsTransformExtractTimepart, and clsOutputCSV” steps to the canvas. 
• Connect the dots between the “clsInputCSV” and other steps, as shown above.
• Define a variable or a global to hold the virtual path.
• Click the "clsInputCSV" step to configure its "Required" properties. Provide a name for the step. Specify the path to the input CSV file on the application server. Select the ETL data schema from the drop-down list. Note: The schema's column names should match those in the input file. Click the Save button. Click here to learn about the ETL Data Schema Designer.

• Click the "clsInputCSV" step to view its "Optional" properties. Select the Show Schema button. The pop-up window displays the schema columns as shown below. This function helps to understand the schema at a glance.  

• Click the "clsTransformExtractTimepart" step to configure its "Required" properties. Enter the step name. Click the button to configure the columns to transform. Then click the Save button. 

• Click the "clsTransformExtractTimepart" step to configure its "Required" properties. Click the button to open the transform list. A pop-up window will appear for configuration. Click the Add Row (+) button to insert an empty row. Select the timespan data type column from the drop-down list. Select the time extraction type from the drop-down list for transformation. Select the output column from the drop-down list. You may also type a new Out column name and press Enter to add it. Click the Save button. You may add multiple columns for transformation using the Add Row button. 

• Click the "clsOutputCSV" step to configure its "Required" properties. Enter a name for the step. Enter the path to the output CSV file on the application server. Click the Save button. 

• Click the "clsOutputCSV" step to configure its "Optional" properties. Provide a name for the step. Provide a variable or global reference to store the virtual output path. Click the Save button. 

• The “Logging” configuration is necessary for documentation and also measures workflow progress and percent complete. This is achieved by configuring the step state and percent fields individually, as shown below. Configure the “Logging” using the following properties.

• Save the ETL definition, create a new ETL instance, and execute. Render the ETL instance. Click the ETL step to view its properties. The “clsTransformExtractTimepart” step should extract the datetime portion as configured and write it to the output file. The extracted values can now be used for grouping, filtering, reporting, dashboard creation, or further ETL transformations. The variable/global holds the output file’s virtual path as shown below. 

Tips:

• Validate all source date/time values before extracting individual components.
• Extract only the date/time parts required by downstream processes to minimize unnecessary schema growth.
• Use descriptive destination column names such as OrderYear, InvoiceMonth, or CreatedHour.
• Perform extraction before grouping or aggregation when reports require calendar-based summaries.
• Standardize incoming timestamps when processing data from multiple time zones.
• Test extraction rules using representative datasets before deploying ETL definitions to production.
• Combine this transform with Group By Transform, Pivot Transform, Filter with Conditions, Filter with Expressions, Output Column Transform, JSON Output File, or SQL Output components to build powerful reporting and analytical ETL workflows.

Notes:

• This transformation extracts individual date or time components without changing the original timestamp.
• Multiple extraction rules can be configured within a single transformation.
• Source date/time columns must exist within the ETL schema.
• The extracted values become additional fields available to downstream ETL components.
• Invalid source values or unsupported extraction configurations may result in ETL execution errors.
• Extracting date/time parts is commonly performed before reporting, grouping, or business intelligence processing.
• This transform is frequently used in dashboards, data warehouses, trend analysis, operational reporting, compliance reporting, and enterprise analytics.
• Review downstream mappings whenever new extracted fields are added to the ETL schema.

Transform Datetime by Extracting a Part vs. Transform Time by Format:

Although both transformations work with date/time values, they serve different purposes:

As a general guideline:

• Use Transform Datetime by Extracting a Part when downstream processing requires individual calendar or time components.
• Use Transform Time by Format when the entire timestamp simply needs to be displayed or exported in a different format.

Definition Sample:

You may download the sample ETL definition(s) from the link provided and later import them into your FlowWright ETL Designer.

Note: Verify and complete any missing configuration after importing the sample, including:

• Date/time extraction rules
• Source date/time column selection
• Destination column mappings
• ETL schema validation
• Downstream transformation mappings
• Destination component configuration
• Environment-specific settings

After verifying the configuration, save the ETL Definition before execution.

Click here to download the sample file.