Datetime values can be imported into 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.
When you are formatting a column of date values, you can attempt to standardize the values in the following order.
Through the Column Details panel, you can review the set of patterns that match the values in your date column and select the ones to apply to standardize the values.
Steps:
In the right panel, select the Convert card.
Tip: If you do not see the Convert card, you might try to generate a new random sample, in which example patterns are more evenly distributed throughout the sample. |
Select the Convert card in the Patterns tab |
In some cases, you may not be able to simply select patterns, which generates sufficient suggestions to standardize your date values. A second approach involves keying on mismatched values in the column.
Tip: This technique works for columns in which all values are valid Datetime values but are in different date formats. If you have values that are invalid for any date format, you must use Option 3 to correct the syntax errors using patterns first. See below. |
In this case, you set the data type for the column to Datetime and use the DATEFORMAT function to match the format of the values that you want to change. Next to the values from the preceding table, you can see the corresponding date format token:
myDate | DATEFORMAT value |
---|---|
Mar-14-2018 | MMM-dd-yyyy |
03/14/2018 | MM/dd/yyyy |
2018-Mar-03 | yyyy-MMM-dd |
3/14/18 | M/d/yy |
03/14/2018 00:00:00 | MM/dd/yyyy HH:mm:ss |
March 14, 2018 | MMMM dd, yyyy |
For purposes of this example, suppose your myDate
column contains values in MM/dd/yyyy
and M/d/yy
format. You wish to standardize on MMMM dd, yyyy
format.
Steps:
myDate
column, select Date/Time. In the dialog, select the Date format that matches values you wish to fix:
Date/Time format selector |
For more information on the supported Datetime formats, see Datetime Data Type.
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:
Click Add. All values that matched the MM/dd/yyyy
format are converted to the MMMM dd, yyyy
format.
M/d/yyyy
.For the value in the transformation, insert the following function:
ifvalid($col, ['Datetime','M/d/yyyy'], dateformat(myDate, 'MMMM dd, yyyy')) |
You can reformat dates by providing example output values for a listed source value. For a column of date values, you can begin providing example outputs for individual values, and can perform pattern-based transformations to similarly formatted values. For more information, see Overview of TBE.
Steps:
YYYY
). Select one of the values. Then, select the Replace card. Click Edit.Your transformation should look like the following:
The capture group should look like the following:
({digit}{4}) |
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.
$1
, $2
, $3
, and so on. These tokens refer to the first, second, and third capture groups in the Find value. The Replace value should look like the following:
01/01/$1 |
Your transformation should look like the following when done:
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:
For more information on the supported codes for specifying your own Datetime formats, see Datetime Data Type.
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. |
Steps:
First, you must split the column based on the cell value's delimiter. Note that the following transformation uses the
{delim}
to locate the delimiter in the cell value. This delimiter is either a dash or a slash.
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:
For month:
For day:
You can now bring together these three columns: