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.

Alliance Rule Example:

https://alliance-agiledata-d.appspot.com/app/rules/edit/a5ce22f1-3e82-48fb-9777-4645eeb2c0f3___2024-01-23%2020:50:11.423953+00

Rule Type Format¶

FORMAT_DATE( ‘%d/%m/%Y’ , FieldName )

FORMAT_DATETIME( ‘%d/%m/%Y’ , FieldName )

EXTRACT(MONTH FROM 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¶

2024-25-12:01:03:03 becomes 25-12-2024

Extract Month Number - dd-mm-YYYY (25-12-2024)¶

Rule Type = Calculated Output Field

Rule Attribute = EXTRACT(MONTH FROM FieldName)

Examples¶

25-12-2024 becomes 12

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 systems of capture 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