Description:

The Transform Date by Adding/Subtracting Time step adjusts date values by adding or subtracting configurable time intervals. The transformation is configured through the Configure Transform List property, where ETL designers specify the source date column, the time interval to apply, the arithmetic operation (addition or subtraction), and the destination column.

Enterprise applications frequently require dates to be shifted relative to existing values. Examples include calculating invoice due dates, warranty expiration dates, subscription renewal dates, document retention periods, employee probation end dates, project milestones, and service review dates. This transformation performs these calculations consistently without requiring custom SQL or scripting.

During ETL execution, each configured source date is processed according to the configured operation. The resulting date is written to the destination column and immediately becomes available for downstream ETL transformations.

Unlike Transform Date by Date Difference, which calculates the elapsed interval between two dates, this transformation modifies a single date by applying a configurable amount of time.

The Transform Date by Adding/Subtracting Time step is typically placed after importing and validating date values but before reporting, exporting, database loading, or downstream business processing.

The step supports:

• Date addition
• Date subtraction
• Configurable time intervals
• Multiple date transformations
• Enterprise ETL workflows
• Integration with FlowWright ETL pipelines

Typical business uses include:

• Invoice due date calculation
• Contract renewal scheduling
• Warranty expiration dates
• Subscription lifecycle management
• Project milestone planning
• HR onboarding timelines
• Compliance retention periods

Usage:

The Transform Date by Adding/Subtracting Time step is typically inserted after importing and validating date values but before reporting, filtering, exporting, or loading the transformed dataset into downstream systems.

During execution, each configured source date is adjusted according to its transformation rule. The calculated date immediately becomes available to subsequent ETL components.

A typical ETL pipeline might look like this:

Typical usage scenarios include:

• Calculating payment due dates
• Determining warranty expiration dates
• Scheduling preventive maintenance
• Computing employee probation end dates
• Generating subscription renewal dates
• Preparing compliance retention schedules
• Creating project milestone timelines

The adjusted dates can then be formatted, filtered, grouped, 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 “clsTransformDateDef” example.          

• Create a new ETL definition called “clsTransformDateDef” and open the definition in designer mode. 
• Drag “clsInputCSV, clsTransformDate, 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 "clsTransformDate" 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 "clsTransformDate" step to configure the transform list. The configuration pop-up window opens. Click the Add Row (+) button to insert an empty row. Select the date as the input source column. From the drop-down list, select the operator (add, subtract) and the type (days, months, years). Enter the type value as shown below. Select the date as the output target 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 “clsTransformDate” step should transform the date by adding or subtracting days, months, or years from the output file. The variable or global holds the output file’s virtual path as shown below. The calculated due dates are now available for reporting, notifications, workflow automation, database loading, and downstream integrations.

Tips:

• Validate source date values before applying date arithmetic.
• Preserve the original date column whenever auditability or historical reporting is required.
• Standardize interval definitions across ETL solutions to maintain consistent business calculations.
• Verify downstream business rules for month-end, leap-year, and fiscal calendar scenarios.
• Test date calculations using representative production datasets before deployment.
• Document business rules associated with calculated dates for easier maintenance.
• Combine this transform with Validate Data, Transform Date by Format, Transform Date by Date Difference, Transform Date by Getting Working Days Between Two Dates, Sorting Transform, JSON Output File, or SQL Output components to build comprehensive enterprise date-processing workflows.

Notes:

• Multiple date transformation rules can be configured within a single transformation.
• Source columns must contain valid date values.
• The original source date remains unchanged unless explicitly configured otherwise.
• Calculated dates immediately become available to downstream ETL components.
• Invalid date values or transformation configurations may result in ETL execution errors.
• The supported interval types and arithmetic behavior are determined by the FlowWright ETL engine and the transformation configuration.
• This transform is commonly used for ERP integrations, CRM workflows, HR systems, financial reporting, subscription management, compliance reporting, project management, and enterprise business automation.

Transform Date by Adding/Subtracting Time vs. Transform Date by Date Difference:

Although both transformations perform date-related calculations, they address different business requirements:

As a general guideline:

• Use Transform Date by Adding/Subtracting Time when a new future or past date must be calculated from an existing date.
• Use Transform Date by Date Difference when the elapsed time between two dates needs to be measured.v

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 transformation rules
• Source and destination column mappings
• Time interval definitions
• Addition or subtraction settings
• ETL schema validation
• Destination component configuration
• Environment-specific settings

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

Click here to download the sample file.