.. _format-dates-snip:

======================
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