Format Dates

From the column menu for your date column, select Column Details.

In the Column Details panel, click the Patterns tab.

In the Patterns tab, you can review the set of patterns that describe all values that appear in the column. Select one that needs to be corrected.

In the right panel, select the Convert card.

Figure: Select the Convert card in the Patterns tab

Click Add.

The number of patterns displayed in the Patterns tab is reduced. You can continue to select patterns to standardize values.

Iterate until there is only one pattern displayed in the panel.

From the Data Type menu at the top of the myDate column, select Date/Time.

In the dialog, select the Date format that matches values you wish to fix:

Figure: Date/Time format selector

Click Save.

Now, you need to modify the values that match this format to match the target format (MMMM dd, yyyy). Click the green bar in the column, which matches the values for the currently valid Datetime format., Then click the Set suggestion. Click Modify.

In the Transform Builder, you have a predefined transformation that sets values based on whether the column values are valid for the currently specified data type and format. You must replace the NULL() entry with the DATEFORMAT function which changes these values to the proper format:

myDate

ifvalid($col, ['Datetime','yy','yyyy'], dateformat($col, 'MMMM dd, yyyy'))

Click Add. All values that matched the MM/dd/yyyy format are converted to the MMMM dd, yyyy format.

Repeat the previous steps:

1. Set the column's Datetime format to: M/d/yyyy.

2. Select the green bar in the column data quality bar.

3. Select the Set suggestion and modify it.

4. For the value in the transformation, insert the following function:

   ifvalid($col, ['Datetime','M/d/yyyy'], dateformat(myDate, 'MMMM dd, yyyy'))

5. Add the transformation to you recipe.

Repeat Step 7 for any other mismatched formats.

You may have some manual fixups to complete at the end. See below.

Now that you have selected a specific format for your Datetime values, the rows that do not match this format are now identified as mismatched in the column. Click the red bar at the top of the column.

In the Status bar at the bottom of the screen, click Show only affected rows.

You can now see only the rows that remain mismatched with respect to the preferred Datetime format.

Select one of these values. For example, suppose you have quite a few values that are only four-digit year values (YYYY). Select one of the values. Then, select the Replace card. Click Edit.

Your transformation should look like the following:

UpdateTime

`{start}{digit}{4}{end}`

''

You can modify the search and replace patterns to capture and write back the year value:

1. In the Find value, put parentheses around the pattern that captures the four digits in a row. Adding parentheses around a matching pattern identifies that sub-pattern as a capture group, which can be referenced in any replacement.

2. The capture group should look like the following:

   ({digit}{4})

3. For the Replace with value, you must insert a month and day value according to the format selected for the column (MM/DD/YYYY), followed by a reference back to the capture group.

4. Capture groups from the matching pattern can be referenced in the replacement value using references such as $1, $2, $3, and so on. These tokens refer to the first, second, and third capture groups in the Find value.

5. The Replace value should look like the following:

   01/01/$1

6. Your transformation should look like the following when done:

   Transformation Name	Replace text or patterns
   Parameter: Column	UpdateTime
   Parameter: Find	`{start}({digit}{4}){end}`
   Parameter: Replace with	01/01/$1

Click Add.

You can repeat these steps for the remaining mismatched values.