Format Dates

Sometimes you want to change a date or date time value to show the date in a different format.

For example show it as 28-02-2019, 28/02/2019 or Feb-28 2019.

Rule Type Format

format_date( “%d/%m/%Y” , FieldName )

format_datetime( “%d/%m/%Y” , FieldName )

parse_date( “%Y%m%d” , FieldName )

regexp_extract( FieldName , “[0-9]+[0-9]+[0-9]+[0-9]” )

timestamp_millis( FieldName )

Snippits

Format Date Field - dd-mm-YYYY (28-02-2019)

Rule Type = Calculated Output Field

Rule Attribute = format_date( “%d/%m/%Y” , FieldName )

Examples

  • 2019-28-02 becomes 28-02-2019

Format Date Field - YYYY-mm-dd (2019-02-28)

Rule Type = Calculated Output Field

Rule Attribute = format_date( “%Y/%m/%d” , FieldName )

Examples

  • 28-02-2019 becomes 2019-28-02

Format Date:Time Field - dd-mm-YYYY (28-02-2019)

Rule Type = Calculated Output Field

Rule Attribute = format_datetime( “%Y-%m-%d” , FieldName )

Examples

    • 2019-28-02:01:03:03 becomes 28-02-2019

Dates in Text Fields

If you have a field that has date values in it, but the field is not actually a date or date:time field, you can do a little text parsing to find a part of the date. This will look for the first 4 digit numeric sequence it can find in the text in the field.

Be aware that as the field is a text field, anything can be in that field, so this code is a little hit and miss.

Parse Date from Text Fields

Rule Type = Calculated Output Field

Rule Attribute = parse_date( “%Y%m%d” , FieldName )

Examples

  • 20192802 becomes 28-02-2019

    parse_date( “%Y%m%d” , FieldName )

Parse part of a date from Text Fields

Rule Type = Calculated Output Field

Rule Attribute = regexp_extract(FieldName,”[0-9]+[0-9]+[0-9]+[0-9]”)

Examples

  • ‘7/10/2004’ becomes ‘2004’

  • ‘1993, 1994’ becomes ‘1993’

  • ‘1998-Present’ becomes ‘1998’

  • ‘November 2005’ becomes ‘2005’

  • ‘13 and 14 Oct 2005’ becomes ‘2005’

  • 13/08/01 to 15/01/05 becomes null

Convert from Epoch

Some data factories store the date as a string based on the epoch https://en.wikipedia.org/wiki/Epoch_(computing)

Rule Type = Calculated Output Field

Rule Attribute = timestamp_millis(FieldName)

Examples

  • 1587096281476 becomes 2020-04-17 04:04:41.476 UTC