Transforming the Date Format of a Column

Use this function to change the representation of date.

Take project 1 in the Sample-customer dataset as an example. The date values in the DOB column is in the MM/dd/yy format. To change this format to the MMM d'th',yyyy format:

Procedure

  1. Form the DOB column menu, click Edit columns > Transform date format.
  2. In the New column name field, enter a name or accept the default value for the new column.
  3. Select one of the following automatic guessing options. In this example, select the first option.
    • Auto guess (prefer month first)
    • Auto guess (prefer day first)
    • Source Format
    Note: The auto guess option first tries the specified month day order. But if that option does not produce a valid date, it also tries alternative options. For example, for 10/25/2012, the Auto guess (prefer day first) option still interprets it as Oct 25, 2012, the same is true for 25/10/2012.
  4. From the New format list, select the desired target format. In this example, select MMM d'th', yyyy (Jun 6th, 2000).
  5. If you want to remove the source column after it has been transformed, select the remove this column check box.
  6. Select one of the following error handling options. In this example, select the first option.
    • Set to blank
    • Store error
    • Copy value from original column
  7. Click OK.
    A new column Copy_DOB is created, and all the dates are in the MMM d'th', yyyy format.