Format Dates Transformation: Standardize the Date Format in a Column

Format Dates Transformation: Standardize the Date Format in a Column

In CRM Analytics, if a text (dimension) column contains dates in different formats, use the Format Dates recipe transformation in a Data Prep recipe to standardize the format for all dates in the column. A consistent format enables you to correctly filter and group records by date, including filtering by date component, such as month. It also ensures you can successfully convert the column type from text (dimension) to date. Optionally, you can convert the column type from Text to Date.

1. In a Transform node of a Data Prep recipe, select the text (dimension) column that contains dates in the Preview tab.
2. In the Transform toolbar, click the Format Dates button ().
3. Enter the original date formats in the selected column.
   

   Note The order of the date formats in the Original Formats box matters. For example, if a date is 01/06/2011, the date format could be dd/MM/yyyy or MM/dd/yyyy. The Date Format transformation assumes the first matching format shown the Original Date Format box is the right one.
4. Select the desired date format in the Change To field.
5. To convert a text (dimension) column with dates to a Date column type, select Convert column type to Date.
6. Under Show Results In, select whether the new values appear in a new column and whether to keep the original column.
7. If you elect to create a column, set the label under Column Label.
8. Click Apply to add the transformation to the Transform node.
9. To view the Graph area, click the Collapse button ().

When you run the recipe, the transformation replaces the dates in the existing column with dates in the specified format. If the date's original format can't be determined or the date doesn’t have a value for a date component, the transformation 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.