Description:

The Transform Time by Format step reformats one or more date/time values into user-defined output formats without altering the underlying point in time represented by the value. The transformation is configured through the Configure Time Format property, where ETL designers specify the source column, destination column (if applicable), and the desired output format.

This transformation is particularly useful when source systems use different date or time representations, or when downstream applications require timestamps to follow a specific format. Common scenarios include generating ISO 8601 timestamps, converting between regional date formats, or producing standardized date/time values for reports and exported files.

Unlike Transform Time by Adding/Subtracting Time, which changes the actual date/time value by applying a time offset, Transform Time by Format changes only how the date/time value is represented.

The Transform Time by Format step is typically placed after data validation and before exporting or loading the transformed dataset.

The step supports:

• Date formatting
• Time formatting
• Date/time standardization
• Regional format conversion
• Output formatting
• Enterprise ETL workflows
• Integration with FlowWright ETL pipelines

This step can be used for:

• Standardizing exported data
• Preparing API payloads
• Database import formatting
• Report generation
• Regional date conversion
• ISO date formatting
• Enterprise system integration

Usage:

The Transform Time by Format step is typically used after importing and validating date/time values but before exporting data or loading it into destination systems that require specific timestamp formats.

During execution, each configured date/time value is reformatted according to the specified output pattern while preserving the original date and time.

A typical ETL pipeline might look like this:

Typical formatting scenarios include:

• Converting dates from MM/dd/yyyy to yyyy-MM-dd
• Formatting timestamps using ISO 8601 for REST APIs
• Displaying dates in regional formats for business users
• Preparing database-compatible timestamp values
• Standardizing timestamps from multiple source systems
• Formatting dates for regulatory reports
• Creating consistent output across enterprise integrations

The formatted values can then be exported or consumed by downstream ETL components.

Prerequisite:

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

Example:

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

• Create a new ETL definition called “clsTransformFormatTimeDef” and open the definition in designer mode.
• Drag “clsInputCSV, clsTransformFormatTime, 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 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 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 "clsTransformFormatTime" step to configure its "Required" properties. Provide a name for the step. Click the button to configure the columns to transform. Then click the Save button. 

• Click the "clsTransformFormatTime" step to configure its "Required" properties. Click the button to open the transform list configuration window. Click the Add Row (+) button to insert an empty row. Select the timespan data type column from the drop-down list. Select the time format type for transformation from the drop-down list. 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 “clsTransformFormatTime” step should transform time using the format configured for the output file. The formatted timestamps are now ready for reporting, export, or loading into downstream systems that require a standardized date/time format. The variable or global holds the output file’s virtual path as shown below. 

Tips:

• Validate all date/time values before applying formatting rules.
• Standardize timestamp formats before exporting data to external applications.
• Use internationally recognized formats such as ISO 8601 whenever possible for system integration.
• Preserve the original date/time column by duplicating it first if both the original and formatted values are required.
• Verify formatting rules using representative datasets before deploying ETL definitions to production.
• Consider the date format requirements of downstream databases, APIs, and reporting tools.
• Combine this transform with Duplicate Column Value, Transform Time by Adding/Subtracting Time, Validate Data, Output Column Transform, JSON Output File, or SQL Output components to create complete ETL workflows.

Notes:

• This transformation changes only the format of the date/time value and does not change the underlying date or time.
• Multiple formatting rules can be configured within a single transformation.
• Source date/time columns must exist within the ETL schema.
• Invalid formatting patterns or unsupported date/time values may result in ETL execution errors.
• Formatting operations should typically be performed near the end of the ETL pipeline after all date/time calculations have been completed.
• This transform is commonly used for reporting, API integration, data migration, file exports, compliance reporting, and enterprise data exchange.
• Review downstream integrations whenever output date/time formats are modified.

Transform Time by Format vs. Transform Time by Adding/Subtracting Time:

Although both steps work with date and time values, they serve different purposes:

As a general guideline:

• Use Transform Time by Format when only the appearance or representation of a timestamp needs to change.
• Use Transform Time by Adding/Subtracting Time when the business process requires calculating a new date or time value.

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 formatting rules
• Source date/time column selection
• Output format patterns
• 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.