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