Skip to main content

Format Dates

Datetime values can be imported into Dataprep by Trifacta in a variety of formats.

Below are just a few examples of one date in different acceptable formats:

myDate

Mar-14-2018

03/14/2018

2018-Mar-03

3/14/18

03/14/2018 00:00:00

March 14, 2018

This section describes the tools and approaches for standardizing and formatting your date values.

Custom Datetime Formats

You can create your own customized Datetime formats using the DATEFORMAT function. For example, the following changes the format of the lastDate function to use the yyyy:MM:dd format:

Transformation Name

Edit with formula

Parameter: Columns

lastDate

Parameter: Formula

DATEFORMAT(lastDate, 'yyyy:MM:dd')

Normalize Regional Differences

The following date values correspond to the same date but vary in format in different regions of the world:

Date Value

Region

03/14/2018

U.S.

14/03/2018

E.U.

2014-03-14

China

In the above examples, the delimiters for the U.S. and E.U. values are identical, which makes parsing these values more challenging.

Tip

If your dataset contains date values from different regions of the world, you should find or create a separate column to identify the applicable region.

Suppose the previous set of dates was represented in your dataset with the following values:

contractDate

region

03/14/2018
USA
14/03/2018
EU
2014-03-14
CHN

In this case, you might try the following generalized solution. You can use conditional transformations to extract the day, month, and year values from the contractDate column based on the value in the region column.

Note

This solution assumes that all date values within for a specific region (e.g. USA) are consistently formatted. You should perform those formatting actions first.

Steps:

  1. First, you must split the column based on the cell value's delimiter. Note that the following transformation uses the Wrangle{delim} to locate the delimiter in the cell value. This delimiter is either a dash or a slash.

    Transformation Name

    Split by delimiter

    Parameter: Column

    contractDate

    Parameter: Option

    by Delimiter

    Parameter: Delimiter

    `{delim}`
  2. Create the following three conditional transformations for extracting the day, month, or year values based on the value in the Region column. Here is the transformation to acquire the year values:

    Transformation Name

    conditions

    Parameter: Condition type

    Case on single column

    Parameter: Column to evaluate

    Region

    Parameter: Case 1

    'EU'

    Parameter: Value 1

    contractDate3

    Parameter: Case 2

    'USA'

    Parameter: Value 2

    contractDate3

    Parameter: Case 3

    'CHN'

    Parameter: Value 1

    contractDate1
  3. For month:

    Transformation Name

    conditions

    Parameter: Condition type

    Case on single column

    Parameter: Column to evaluate

    Region

    Parameter: Case 1

    'EU'

    Parameter: Value 1

    contractDate2

    Parameter: Case 2

    'USA'

    Parameter: Value 2

    contractDate1

    Parameter: Case 3

    'CHN'

    Parameter: Value 1

    contractDate2
  4. For day:

    Transformation Name

    conditions

    Parameter: Condition type

    Case on single column

    Parameter: Column to evaluate

    Region

    Parameter: Case 1

    'EU'

    Parameter: Value 1

    contractDate1

    Parameter: Case 2

    'USA'

    Parameter: Value 2

    contractDate2

    Parameter: Case 3

    'CHN'

    Parameter: Value 1

    contractDate3
  5. You can now bring together these three columns:

    Transformation Name

    Merge columns

    Parameter: Columns

    day, month, year

    Parameter: Separator

    '/'

    Parameter: New column name

    newDate
  6. You now have your new date column. You may need to reformat it into a preferred format.

  7. Delete the columns that were created during this process.