Function Friday – Formatting and Parsing Dates and Times

This week I’m looking at formatting and parsing date and time values in Power Automate. This can be a great pain when working with data in your functions for a straightforward reason: DateTime values come in many different formats. And different systems will require different formats for your DateTime values. And that’s before you even consider the pain of timezones.

formatDateTime

We’ll first look at formatting. The formatDateTime function takes a DateTime string and converts it into a different format. The pattern is as follows:

formatDateTime('<value>', '<format'>, '<locale>')

Only the input value is required for the function. It must be a string and it must be in a format that the function can recognize and understand. If your input value is not in such a format, you will first need to parse the input value (see parseDateTime function below).

The format value will be the format that you want the output value to be. The default format if you don’t provide one will be the “o” format, which represents ISO 8601 format.

yyyy-MM-ddTHH:mm:ss.fffffffK

• yyyy: the year in 4-digit format
• MM: month of the year in 2 digit format
• dd: day of the month in 2 digit format
• T is a separator between the date portion and the time portion of the value
• HH: The hour value in 24-hour, 2-digit format
• mm: The minutes in 2-digit format
• ss: The seconds in 2-digit format
• fffffff: The fraction of the second down to 7 digits
• K represents the time zone. For example, if the value is just “Z”, that stands for UTC time, which is generally the default. Otherwise, it’s generally represented as a +/- difference between UTC and the timezone being represented. For example, “-05:00” for Eastern US time on standard time and “-04:00” would be Eastern US on daylight savings time.

As an example, in this format, August 19, 2022, at precisely 10 AM Eastern US time would be:

2022-08-19T10:00:00.0000000-04:00 // as Eastern US time
2022-08-19T14:00:00.0000000Z // as UTC time

Follow these links to read more about the standard and custom DateTime formats available:

• Standard Date and Time Formats
• Custom Date and Time Formats

The locale determines how the DateTime value is presented visually. The default is “en-us”, which represents English (US). If the format is ISO 8601, then the locale really doesn’t matter. The locale only matters when you are using a different format that represents the date or time portions as words instead of numbers. Take this example from the Microsoft documentation:

formatDateTime('01/31/2016', 'dddd MMMM d') // returns 'Sunday, January 31'
formatDateTime('01/31/2016', 'dddd MMMM d', 'es-es') // returns 'domingo enero 31'

parseDateTime

The parseDateTime function takes a DateTime value in a string and parses it into “o” (ISO 8601) format. The pattern is as follows:

parseDateTime('<value>', '<locale'>, '<format>')

Be careful with this function. While, once again, the locale and format are optional parameters, they are reversed in their order from the formatDateTime function. Here, the locale is second and the format is third. It’s easy to get them backward and have your function fail in a confusing manner.

If the function can recognize the format and locale, then it will work fine without those parameters. However, more often than not, if you have a need to parse a DateTime value, it’s because Power Automate can’t recognize the data being passed to it and you’ll have to tell it how to recognize the value and convert it into “o” format.

The most common use I’ve found for parseDateTime is when I’m importing dates that are in non-US formats (i.e. dd/MM/yyyy as opposed to MM/dd/yyyy) since most of the world formats dates in one pattern and the US decided to do the opposite. I’ve also used it quite frequently for DateTime values that only have 2-digit years (i.e. 10/15/00). The last pattern I have seen quite a bit is when the input date doesn’t have any formatting (i.e. 20220819).

In all such cases, Power Automate won’t be able to automatically determine what the date is from the input and the parseDateTime function will be needed.

Some examples:

parseDateTime('19/08/2022', 'es-es') // returns ('2022-08-19T00:00:00.0000000')
parseDateTime('10/15/00', 'en-us', 'MM/dd/yy') // returns ('2000-10-15T00:00:00.0000000')

There’s one thing that’s important to remember. If your input format doesn’t specify any timezone information, the output doesn’t include it either. It will then be up to your process to determine how to handle that. Do you assume UTC? Do you assume local time? That’s part of the joys of dealing with timezones on DateTime values, and Power Automate isn’t any easier than any other language or process to deal with those quandaries.

Conclusion

Anywhere you work with dates and times in programming, it’s a mess. I would love to someday see a solution that resolves the pain. I don’t know what that solution would be, and I know that it will never happen in my lifetime. But it would be nice to hope that someday it might happen. In the meantime, the parseDateTime and formatDateTime functions make your life in Power Automate a little bit easier.

The post Function Friday – Formatting and Parsing Dates and Times first appeared on Barret Codes.