format-date-column
Description
format-date-column adaptor formats date/time values in a datatable column.
Define the format of a date/time column and either replace the original column values or add a new column with the reformatted date-time.
Inputs
data
Type: datatable
Required: Yes
The datatable containing the column to be formatted.
original column name
Type: text
Required: Yes
The name of the column containing the date/time values.
original format
Type: text
Required: No
The format tokens, of the original values, based on Unicode Technical Standard #35. If unspecified, defaults to ISO 8601 date/time.
original locale
Type: text
Required: No
The language locale to be used when parsing the original values. If unspecified, locale defaults to en-US.
new format
Type: text
Required: No
The format tokens, of the new values, based on Unicode Technical Standard #35. If unspecified, defaults to ISO 8601 date/time.
new locale
Type: text
Required: No
The language locale to be used when formatting the new values. If unspecified, locale defaults to en-US.
new column name
⁃ Type: text
⁃ Required: No
The name of the column to which the new values will be added. If unspecified, the new values will be added to the original column (specified by original column name), overwriting the original values.
Outputs
data
Type: datatable
A datatable with formatted values.
Examples
Example 1: Default behaviour.
Inputs:
data:
| id | code | name | date |
|---|---|---|---|
1 | GB | United Kingdom | 2007-01-29T00:00:00+01:00 |
2 | TR | Turkey | 2008-02-29T00:00:00+01:00 |
3 | US | United States | 2009-03-29T00:00:00+01:00 |
4 | IND | India | 2010-04-29T00:00:00+01:00 |
original column name: date
original format: null (empty)
original locale: null (empty)
new format: dd MMM yyyy
new locale: name
new column name: null (empty)
Outputs:
data:
| id | code | name | date |
|---|---|---|---|
1 | GB | United Kingdom | 28 Jan 2007 |
2 | TR | Turkey | 28 Jun 2017 |
3 | US | United States | 18 Jan 2005 |
4 | IND | India | 10 May 2012 |
-> Overwrote the date column with a date in dd MMM yyyy format
Example 2: Date reformatted in new column.
Inputs:
data:
| id | code | name | date |
|---|---|---|---|
1 | GB | United Kingdom | 2007-01-29T00:00:00+01:00 |
2 | TR | Turkey | 2008-02-29T00:00:00+01:00 |
3 | US | United States | 2009-03-29T00:00:00+01:00 |
4 | IND | India | 2010-04-29T00:00:00+01:00 |
original column name: date
original format: null (empty)
original locale: null (empty)
new format: dd MMM yyyy
new locale: name
new column name: new date
Outputs:
data:
| id | code | name | date | new date |
|---|---|---|---|---|
1 | GB | United Kingdom | 2007-01-29T00:00:00+01:00 | 28 Jan 2007 |
2 | TR | Turkey | 2017-06-29T00:00:00+01:00 | 28 Jun 2017 |
3 | US | United States | 2005-01-19T00:00:00+01:00 | 18 Jan 2005 |
4 | IND | India | 2012-05-11T00:00:00+01:00 | 10 May 2012 |
-> Created a new date column with the date in dd MMM yyyy format, derived from date column
Use Cases
Standardize date/time formats across different data sources where date/time data are stored in different formats
Simplify date-time into simple date
Reformat date to align with a new target audience
Reformat date to a format more useful for visualisation
Last updated