Converting dates from one format to another

Use a Python expression to convert dates from one format to another.

Introduction

Enter a Python expression into the addfieldx or convertx transformation to convert dates from one format to another. For example, use a Python expression to:

Convert a date from YYYY-MM-DD to MM-DD-YY
Convert a time from HH:MM:SS to HH:MM

Use the addfieldx transformation to keep the original dates and add the converted dates to a new column in the data extract.

Use the convertx transformation to overwrite the original dates with the converted dates.

The examples below demonstrate how the Python expressions can convert the dates from one format to another.

This video guide also explains how to use the convertxcustom script to convert dates from one format into another.

Configuring the Python expression

To convert dates from one format to another, enter the following Python expression into the transformation:

__import__('datetime').datetime.strptime({date_column_name}, 'date_format_to_convert').strftime('format_to_use')

Change the following parameters to configure the Python expression:

date_column_name - This is the name of the column that contains the dates in the format to convert.
date_format_to_convert - This is the current format of the date to convert. Enter the date format to convert using date placeholders. For example, the date 01-03-2022 (1 March 2022) is in the date format %d-%m-%Y.
format_to_use - This is the new format for the dates. Enter the new date format using date placeholders.

See the examples section below for a worked example.

Rules for the Python expression

When configuring the Python expression for this use case, it is important to remember the following rules:

The column name parameter is case sensitive. Enter the name of the column exactly as it appears in the data extract.
Enter the name of the column between two braces (also known as curly brackets). For example, if the name of the column is Dates, enter {Dates}.
Use the Python date placeholders when entering a date format. For a full list of available placeholders, see the Python documentation. Some of the more common date placeholders are as follows:
- %d - The day of the month as a zero-padded decimal number. For example, the 1st of the month is 01 and the 10th of the month is 10.
- %m - The month as a zero-padded decimal number. For example, February is 02 and November is 11.
- %Y - The year as a four character number. For example, 1999 or 2022.
- %y - The year as a two character number. For example, 1999 is 99 and 2022 is 22.
Enter dates between two inverted commas. For example, enter '%d-%m-%Y'.
The format of the date to convert must exactly match the format in the column containing the dates. This includes the characters between the date placeholders. For example, if the dates to convert are in the format DD-MM-YYYY, then the date format to enter into Python expression is '%d-%m-%Y'. The date formats of '%d/%m/%Y' or '%d.%m.%Y' would fail because the characters between the date placeholders are wrong.

Example 1 - addfieldx

This example uses the addfieldx transformation to keep the original dates and add the converted dates to a new column in the data extract. The entered Python expression looks at a column called Dates, converts the dates from MM/DD/YYYY to DD-MM-YYYY and adds the converted dates to a new column called Corrected Dates in the data extract.

For more information on the addfieldx transformation configuration, see the addfieldx transformation reference.

Transformation configuration

Field Name: Corrected Dates
Python Expression: __import__('datetime').datetime.strptime({Dates},'%m/%d/%Y').strftime('%d-%m-%Y')
Field Index: -1

Data table before transformation

Conversion ID	Dates
4310177778	09/29/2021
4328218379	01/05/2022
5260818288	03/27/2022
2028958545	08/07/2021
4393034066	03/13/2022
4989141466	09/04/2021
4911856467	07/11/2022

Data table after transformation

Conversion ID	Dates	Corrected Dates
4310177778	09/29/2021	29-09-2021
4328218379	01/05/2022	05-01-2022
5260818288	03/27/2022	27-03-2022
2028958545	08/07/2021	07-08-2021
4393034066	03/13/2022	13-03-2022
4989141466	09/04/2021	04-09-2021
4911856467	07/11/2022	11-07-2022

Example 2 - convertx

This example uses the convertx transformation to overwrite the original dates with the converted dates. The entered Python expression looks at a column called Dates and converts the dates from MM/DD/YYYY to DD-MM-YYYY.

For more information on the convertx transformation configuration, see the convertx transformation reference.

Transformation configuration

Field: Dates
Python Expression: __import__('datetime').datetime.strptime({Dates},'%m/%d/%Y').strftime('%d-%m-%Y')
Fail on Error: Select this field.

Data table before transformation

Conversion ID	Dates
4310177778	09/29/2021
4328218379	01/05/2022
5260818288	03/27/2022
2028958545	08/07/2021
4393034066	03/13/2022
4989141466	09/04/2021
4911856467	07/11/2022

Data table after transformation

Conversion ID	Dates
4310177778	29-09-2021
4328218379	05-01-2022
5260818288	27-03-2022
2028958545	07-08-2021
4393034066	13-03-2022
4989141466	04-09-2021
4911856467	11-07-2022