You are here:
Format Dates Transformation: Standardize the Date Format in a Column
Standardize the format for all dates in a text column with the Format Dates transformation. With a consistent format, you can correctly filter and group records by date, including filtering by date component, such as month. A consistent format also ensures that you can successfully convert the column type from Text to Date.
- In a Transform node, select the text column that contains dates in the Preview tab.
- In the Transform toolbar, click the Format Dates button (
). - Enter the original date formats in the selected column. The Date Format transformation
assumes the first matching format shown in the Original Formats search box is the correct
one. For example, if a date is 01/06/2023, the date format could be
dd/MM/yyyyorMM/dd/yyyy. - Select the desired date format in the Change To field.
- To convert a text column with dates to a Date column type, select Convert column type to Date.
- Under Show Results In, select whether the new values appear in a new column and whether to keep the original column.
- If you elect to create a column, set the label under Column Label.
- To add the transformation to the Transform node, click Apply.
- To view the canvas area, click the Collapse button (
).
When you run the batch data transform, the transformation replaces the dates in the existing
column with dates in the specified format. If the transformation can't determine the date's
original format or the date doesn’t have a value for a date component, then it replaces the
date with null. For example, if you standardize on the MM/dd/yyyy:hh:mm:sssz format and a date value doesn’t have the seconds date
component (sssz), then the transformation replaces the
date with null.
