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