Description:

The Transform Date by Extracting a Part step extracts one or more individual components from date values using configurable extraction rules. The transformation is configured through the Configure Transform List property, where ETL designers specify the source date columns together with the required date parts to extract.

Enterprise reporting and analytics frequently require individual date components instead of complete date values. For example, financial reports may group transactions by fiscal year or quarter, sales dashboards may summarize monthly revenue, and workforce analytics may compare hiring trends by month or weekday. This transformation makes those individual components available without requiring custom scripting or SQL expressions.

Each configured date column is evaluated during ETL execution, and the selected date component is extracted into the configured destination column. The resulting values become immediately available for downstream ETL transformations.

Unlike Transform Date by Format, which changes the presentation of an entire date value, this transformation extracts a specific element from the date for analytical or business-processing purposes.

The Transform Date by Extracting a Part step is commonly placed after data validation and before grouping, aggregation, filtering, reporting, exporting, or database loading.

The step supports:

• Year extraction
• Month extraction
• Day extraction
• Quarter extraction
• Week extraction
• Day-of-week extraction
• Multiple extraction rules
• Enterprise ETL workflows
• Integration with FlowWright ETL pipelines

Typical business uses include:

• Sales reporting by month
• Financial reporting by quarter
• Workforce analysis by hire year
• Customer registration trends
• Regulatory reporting
• Business intelligence dashboards
• Data warehouse preparation

Usage:

The Transform Date by Extracting a Part step is typically inserted after importing and validating date values but before grouping, filtering, aggregation, reporting, exporting, or loading the transformed dataset into downstream systems.

During execution, each configured date column is processed according to the selected extraction rule. The resulting values immediately become available for downstream ETL components.

A typical ETL pipeline might look like this:

Typical usage scenarios include:

• Extracting invoice years for annual reporting
• Grouping sales by calendar month
• Creating quarterly financial summaries
• Reporting customer registrations by weekday
• Analyzing employee hiring trends
• Building executive KPI dashboards
• Preparing dimensional data for a data warehouse

The extracted values can then be grouped, filtered, sorted, aggregated, exported, or loaded into downstream business systems.

Prerequisite:

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

Example:

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

• Create a new ETL definition called “clsTransformExtractDatepartDef” and open the definition in designer mode. 
• Drag “clsInputCSV, clsTransformExtractDatepart, and clsOutputCSV” steps to the canvas. 
• Connect the dots between the “clsInputCSV” step and other steps, as shown above.
• Define a variable or a global variable 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 "clsTransformExtractDatepart" step to configure its "Required" properties. Provide a name for the step. Click the button to configure the transform list, then click Save. 

• Click the "clsTransformExtractDatepart" step to configure the transform list. A pop-up window will appear. Click the Add Row (+) button to insert an empty row. Select the start date column from the drop-down list. Select the type (days, months, years, hours, minutes, seconds). Select the target date column. Click the Save button. You may add multiple columns for transformation by 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 “clsTransformExtractDatepart” step should extract a specific part of the dates and write it to the output file. The variable or global holds the output file’s virtual path as shown below. 

Tips:

• Validate date values before extracting date components.
• Extract only the date parts required for downstream processing to simplify ETL definitions.
• Use extracted year and month values to improve reporting and dashboard performance.
• Consider creating dedicated destination columns to preserve the original date values.
• Standardize fiscal and calendar reporting conventions across ETL processes.
• Test extraction rules using representative production datasets before deployment.
• Combine this transform with Validate Data, Transform Date by Format, Group By Transform, Filter with Conditions, Sorting Transform, JSON Output File, or SQL Output components to build comprehensive date-processing workflows.

Notes:

• Multiple extraction rules can be configured within a single transformation.
• Source columns must contain valid date values.
• The original date values remain unchanged unless explicitly overwritten.
• Extracted values immediately become available to downstream ETL components.
• Invalid date values or extraction configurations may result in ETL execution errors.
• This transform is commonly used for business intelligence, financial reporting, ERP migration, CRM reporting, regulatory reporting, customer analytics, workforce analytics, and enterprise data warehousing.
• Review downstream reports whenever date extraction rules or reporting calendars are modified.

Transform Date by Extracting a Part vs. Transform Date by Format:

Although both transformations operate on date values, they serve different purposes:

As a general guideline:

• Use Transform Date by Extracting a Part when individual date components are required for analysis, grouping, or reporting.
• Use Transform Date by Format when the entire date must be converted into a standardized presentation 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 extraction rules
• Source and destination column mappings
• Required date parts
• 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.