Salesforce Docs Portal

Standardize Date Formats

If a dimension field contains dates in different formats, use the Format Dates recipe transformation to standardize the format for all values in the field. A consistent format enables you to correctly filter and group records by date, including filtering by date component, such as month. It also ensures that you can successfully convert the field type from dimension to date.

Required Editions

User Permissions Needed
To create a recipe: Edit Analytics Dataflows OR Edit Dataset Recipes
Note
Note To format dates in a Data Prep recipe, see Format Dates Transformation: Standardize the Date Format in a Column.
  1. On the column header of the dimension field, click Column menu button.
    The column dropdown shows multiple transformations, including the Format Dates transformation.
  2. Select Format Dates.
    Analytics detects the formats of the dates in the field.
    The Add Transformation window shows the original date formats used in the column and allows you to specify the standard date format to show in the dataset.
    Note
    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.
  3. If a date format isn’t detected, enter it in the Original Formats box.
  4. To convert a dimension field with dates to a date field type, select Convert field type to date.
  5. Select the desired date format in the Change To field.
  6. Click Add and save the recipe.

When you run the recipe, the transformation creates a field with the dates standardized in the selected format. If CRM Analytics can’t determine the original format 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), CRM Analytics replaces the date with null.