Parse Date 🧽📅

Danny Roden
Danny Roden Administrator
edited December 2023 in Functions library

Parse Date

image.png


This function is designed to have flexibility to accommodate all sorts of different date formats, which can be configured by the user. In addition to mapping the input, as you can see, the user is prompted for a 'format' when using this function and this can be configured intuitively as you can see in some of the below examples.

The function returns a date, parsing an input string based on the format supplied. Uses combinations of yyyy for year, mm for month and dd for day (with special characters defined as and if necessary).

See below for a screenshot with some example results displayed:

image.png


Compatibility:

This function is compatible with all instances of Data Studio from v2.5.1 onwards.

Parse Date.dmx


Tagged:

Comments

  • Henry Simms
    Henry Simms Administrator

    I've created a variation of this function that parses text into dates using the Regular Expression Replace function. Specifically, it uses regex capture groups to capture day, month and year separately and then use them as the replacement values, slash-separated in way that the "Convert To Date" function will understand.

    While it sacrifices some of the usability of the function described above, it will be more efficient and is easier to understand if you understand the basics of regex.

    This version of the function takes text input in the format ddMMyyyy, and converts it to a date. Some examples:

    • Day and month values may or may not have leading zeros, so 13012000 (ddMyyyy) and 1312000 (ddMyyyy) will both parse to 13th Jan 20201
    • Both day and month can be single digit: 342000 (dMyyyy) will become 3rd April 2000
    • Where it's not clear whether day or month is single digit, and both would make a valid date, we assume that the day is single, so 2121988 (dMMyyyy) is 2nd Dec, not 21st Feb
    • But if the date can only be valid in one way, we parse based on that format: 2321988 (ddMyyy) parses to 23rd Feb
    • Any string that cannot be converted to a date returns an error (e.g. "Not a date = "29/02/1999")

    The function:

    image.png

    Search value regex: ^(0?[1-9]|[12][0-9]|3[01])(0?[1-9]|1[0-2])(\d{4})$

    Replacement value: $1/$2/$3 (take the result of each capture group, separated by a forward-slash

    Example results:

    image.png

    Compatibility:

    This function is compatible with all instances of Data Studio from v2.7.6 onwards.

    Convert ddMMyyyy to date.dmx


Categories