Select Page

## Overview

Math (or Mathematical) functions are used to perform a variety of mathematical and scientific operations, such as addition and multiplication. If you come from the BizTalk Server background or are migrating BizTalk Server projects, they are the equivalent of Mathematical and Scientific Functoids inside BizTalk Mapper Editor.

## Available Functions

The Math functions are:

• Absolute: Returns the absolute value of the specified number.
• Arctangent: Returns the arc tangent of a number.
• Ceiling: Returns the smallest integral value greater than or equal to the specified number.
• Cosine: Returns the cosine for the specified angle.
• Divide: Returns the result from dividing two numbers.
• Exponential: Raises the “e” constant to the specified power and returns the result.
• Exponential (base 10): Returns the number 10 raised to the specified power.
• Floor: Returns the largest integral value less than or equal to the specified number.
• Integer divide: Divides two numbers and returns the integer part from the result.
• Log: Returns the logarithm for the specified number in the specified base.
• Log (base 10): Returns the base 10 logarithm for the specified number.
• Modulo: Returns the remainder from dividing the specified numbers.
• Multiply: Returns the product from multiplying two or more specified numbers.
• Power: Returns the specified number raised to the specified power.
• Round: Rounds a value to the nearest integer or the specified number of fractional digits and returns the result.
• Sine: Returns the sine for the specified angle.
• Square root: Returns the square root for the specified number.
• Subtract: Subtracts the second number from the first number and returns the result.
• Tangent: Returns the tangent for the specified angle.

## Absolute

This function states that it will return the absolute value of the specified number.

Behind the scenes, this function is translated to the following XPath function: abs(\$arg)

• `fn:abs`(`\$arg as xs:numeric?`)` as xs:numeric?`

Rules:

• If `\$arg` is negative, the function returns `-\$arg`. Otherwise, it returns `\$arg`.
• For the four types `xs:float``xs:double``xs:decimal` and `xs:integer`, it is guaranteed that if the type of `\$arg` is an instance of type T then the result will also be an instance of T. The result may also be an instance of a type derived from one of these four by restriction. For example, if `\$arg` is an instance of `xs:positiveInteger` then the value of `\$arg` may be returned unchanged.
• For `xs:float` and `xs:double` arguments, if the argument is positive zero or negative zero, then positive zero is returned. If the argument is positive or negative infinity, positive infinity is returned.

Sample:

• The expression `fn:abs(10.5)` returns `10.5`.
• The expression `fn:abs(-10.5)` returns `10.5`.

This function states that it will return the sum from adding two or more numbers.

Behind the scenes, this function is translated to the following XPath expression: \$arg1 + \$arg2 (allows more inputs)

• `(\$arg1 as xs:numeric + \$arg2 as xs:numeric) as xs:numeric`

Rules:

• Returns the arithmetic sum of its operands: (`\$arg1 + \$arg2`).
• `\$arg1 and \$arg2` are numeric values (`xs:float``xs:double``xs:decimal` and `xs:integer`)
• This function allows two or more inputs.

Sample:

• The expression (`1) + (3)` returns `4`.
• The expression (`1.12) + (2)` returns `3.12`.

## Arctangent

This function states that it will return the arc tangent of a number.

Behind the scenes, this function is translated to the following XPath function: math:atan(\$arg)

• `math:atan`(`\$arg as xs:double?`)` as xs:double?`

Rules:

• If `\$arg` is a non-numeric value, then the result is `empty`.

Sample:

• The expression `math:atan(0)` returns `0`.
• The expression `math:atan(1.28)` returns `0.9075933340888034`.

## Ceiling

This function states that it will return the smallest integral value greater than or equal to the specified number.

Behind the scenes, this function is translated to the following XPath function: ceiling(\$arg)

• `fn:ceiling`(`\$arg as xs:numeric?`)` as xs:numeric?`

Rules:

• The function returns the smallest (closest to negative infinity) number with no fractional part that is not less than the value of `\$arg`.
• For the four types `xs:float``xs:double``xs:decimal` and `xs:integer`, it is guaranteed that if the type of `\$arg` is an instance of type T then the result will also be an instance of T. The result may also be an instance of a type derived from one of these four by restriction. For example, if `\$arg` is an instance of `xs:decimal` then the result may be an instance of `xs:integer`.

Sample:

• The expression `fn:ceiling(10.5)` returns `11`.
• The expression `fn:ceiling(-10.5)` returns `-10`.

## Cosine

This function states that it will return the cosine for the specified angle.

Behind the scenes, this function is translated to the following XPath function: math:cos(\$arg)

• `math:cos`(`\$`arg` as xs:double?`)` as xs:double?`

Rules:

• If `\$`arg is positive or negative zero, the result is `\$`arg.

Sample:

• The expression `math:cos(0)` returns `1`
• The expression `math:cos(1212)` returns 0.7931914936378434

## Divide

This function states that it will return the result from dividing two numbers.

Behind the scenes, this function is translated to the following XPath function: \$arg1 div \$arg2

• \$arg1 div \$arg2` as xs:numeric?`

Rules:

• For the four types `xs:float``xs:double``xs:decimal` and `xs:integer`, it is guaranteed that if the type of `\$arg` is an instance of type T then the result will also be an instance of T. The result may also be an instance of a type derived from one of these four by restriction. For example, if `\$arg` is an instance of `xs:decimal` then the result may be an instance of `xs:integer`.

Sample:

• The expression `(10) div (3)` returns `3`.
• The expression `(-3) div (-2)` returns `1.5`.
• The expression `(-3) div (2)` returns `-1`.
• The expression `(-3.5) div (3)` returns `0.7`.

## Exponential

This function states that it will raise the “e” constant to the specified power and returns the result.

Behind the scenes, this function is translated to the following XPath function: math:exp(\$arg)

• `math:exp`(`\$arg as xs:double?`)` as xs:double?`

Rules:

• Returns the value of e

Sample:

• The expression `math:exp(0)` returns `1`.
• The expression `math:exp(1)` returns `2.7182818284590455`.

## Exponential (base 10)

This function states that it will return the number 10 raised to the specified power.

Behind the scenes, this function is translated to the following XPath function: math:exp10(\$arg)

• `math:exp10`(`\$arg as xs:double?`)` as xs:double?`

Rules:

• Returns the value of `10`

Sample:

• The expression `math:exp10(0)` returns `1`
• The expression `math:exp10(1)` returns `1.0e1`.
• The expression `math:exp10(0.5)` returns `3.1622776601683795`

## Floor

This function states that it will return the largest integral value less than or equal to the specified number.

Behind the scenes, this function is translated to the following XPath function: floor(\$arg)

• `fn:error`(`\$code as xs:QName?``\$description as xs:string`)` as none`

Rules:

• The function returns the largest (closest to positive infinity) number with no fractional part that is not greater than the value of `\$arg`.
• For the four types `xs:float``xs:double``xs:decimal` and `xs:integer`, it is guaranteed that if the type of `\$arg` is an instance of type T then the result will also be an instance of T. The result may also be an instance of a type derived from one of these four by restriction. For example, if `\$arg` is an instance of `xs:decimal` then the result may be an instance of `xs:integer`.

Sample:

• The expression `fn:floor(10.5)` returns `10`.
• The expression `fn:floor(10.9)` returns `10`.
• The expression `fn:floor(-10.5)` returns `-11`.

## Integer divide

This function states that it will divide two numbers and returns the integer part from the result.

Behind the scenes, this function is translated to the following XPath expression: (\$arg1) idiv (\$arg2)

• `(\$arg1) idiv (\$arg2) as xs:integer`

Rules:

• Performs an integer division.
• For the four types `xs:float``xs:double``xs:decimal` and `xs:integer`, it is guaranteed that if the type of `\$arg` is an instance of type T then the result will also be an instance of T. The result may also be an instance of a type derived from one of these four by restriction. For example, if `\$arg` is an instance of `xs:decimal` then the result may be an instance of `xs:integer`.

Sample:

• The expression `(10) idiv (3)` returns `3`.
• The expression `(-3) idiv (-2)` returns `1`.
• The expression `(-3) idiv (2)` returns `-1`.
• The expression `(-3.5) idiv (3)` returns `-1`.

Stay tune for the second part of this blog post.

Hope you find this helpful! So, if you liked the content or found it useful and want to help me write more, you can buy (or help buy) my son a Star Wars Lego!

##### Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc.

He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.

## Overview

Utility functions are used to perform a variety of additional and distinct operations that don’t fit in the above Categories, such as stopping a transformation and returning the specified error code and description or format, etc.

## Available Functions

The Utility functions are:

• Copy: Copies any and all of the input’s substructure.
• Error: Stops a transformation and returns the specified error code and description.
• Format date: Returns a date in the specified format.
• Format DateTime value: Returns a timestamp in the specified format.
• Format number: Returns a number in the specified format.
• Format time: Returns a time in the specified format.

## Copy

This function states that it will copy any and all of the input’s substructure. It enables your maps to use schemas that include any and anyAttribute elements. These elements are, in essence, wildcards provided in the Schema definition language to match unknown structures or attributes.

The following figure shows the Copy function used in a map.

Behind the scenes, this function is translated to the following XPath expression: copy-of(\$arg)

```

```

Rules:

• The Copy function copies the element in the input instance message corresponding to the source schema node connected to the Copy function. The function also copies any and all of its substructure and re-creates it in the output instance message at the linked node in the destination schema. Thus, you can also use the Copy function to copy any source and destination records having identical substructures.

## Error

This function states that it will stop a transformation and returns the specified error code and description.

Behind the scenes, this function is translated to the following XPath function: error(\$code, \$description)

• `fn:error`(`\$code as xs:QName?``\$description as xs:string`)` as none`

Rules:

• Calling the `fn:error` function raises an application-defined error.
• This function never returns a value. Instead, it always raises an error. The effect of the error is identical to the effect of dynamic errors raised implicitly, for example, when an incorrect argument is supplied to a function.
• The `\$code` is an error code that distinguishes this error from others. It is an `xs:QName`; the namespace URI conventionally identifies the component, subsystem, or authority responsible for defining the meaning of the error code, while the local part identifies the specific error condition. The namespace URI `http://www.w3.org/2005/xqt-errors` is used for errors defined in this specification; other namespace URIs may be used for errors defined by the application.
• The `\$description` is a natural-language description of the error condition.

## Format date

This function states that it returns a date in the specified format.

Behind the scenes, this function is translated to the following XPath function: format-date(\$arg1, \$arg2)

• `fn:format-date`(`\$value as xs:date?``\$picture as xs:string`)` as xs:string?`

Rules:

• The `\$arg`1 needs to be an xs:date in the following format yyyy-MM-DD or yyyy-MM-DDZ.
• The `\$arg`2 (or picture) consists of a sequence of variable markers and literal substrings. A substring enclosed in square brackets is interpreted as a variable marker; substrings not enclosed in square brackets are taken as literal substrings. The literal substrings are optional and, if present, are rendered unchanged, including any whitespace. The variable markers are replaced in the result by strings representing aspects of the date and/or time to be formatted. These are described in detail below:
• Y – year (absolute value);
• M – month in the year
• D – day in the month
• d – day in the year
• F – day of the week
• W – week in year
• w – week in the month
• C – calendar: the name or abbreviation of a calendar name
• E – era: the name of a baseline for the numbering of years, for example, the reign of a monarch

Sample:

• The expression `fn:`format-date(\$d, “[Y0001]-[M01]-[D01]”) returns 2002-12-31
• The expression `fn:format-date(\$d, "[M]-[D]-[Y]")` returns 12-31-2002.
• The expression `fn:format-date(\$d, "[D1] [MI] [Y]")` returns `31 XII 2002`.
• The expression `fn:`format-date(\$d, “[D1o] [MNn], [Y]”) returns 31st December, 2002
• The expression `fn:format-date(\$d, "[D01] [MN,*-3] [Y0001]")` returns 31 DEC 2002.
• The expression `fn:format-date(\$d, "[YWw]")` returns `Two Thousand and Three`.

## Format DateTime value

Returns a timestamp in the specified format.

This function states that it returns the specified value as an integer.

Behind the scenes, this function is translated to the following XPath function: format-dateTime(\$arg1, \$arg2)

• `fn:format-dateTime`(`\$value as xs:dateTime?``\$picture as xs:string`)` as xs:string?`

Rules:

• The `\$arg`1 needs to be an xs:dateTime in the following format yyyy-MM-DDTHH:mm:ssZ or yyyy-MM-DDTHH:mm:ss-hh:mm.
• The `\$arg`2 (or picture) consists of a sequence of variable markers and literal substrings. A substring enclosed in square brackets is interpreted as a variable marker; substrings not enclosed in square brackets are taken as literal substrings. The literal substrings are optional and, if present, are rendered unchanged, including any whitespace. The variable markers are replaced in the result by strings representing aspects of the date and/or time to be formatted. These are described in detail below:
• Y – year (absolute value);
• M – month in the year
• D – day in the month
• d – day in the year
• F – day of the week
• W – week in year
• w – week in the month
• H – hour in the day (24 hours)
• h – hour in half-day (12 hours)
• P – am/pm marker
• m – minute in the hour
• s – second in a minute
• f – fractional seconds
• Z – timezone
• z – timezone (same as Z, but modified where appropriate to include a prefix as a time offset using GMT, for example, GMT+1 or GMT-05:00. For this component, there is a fixed prefix of GMT or a localized variation thereof for the chosen language, and the remainder of the value is formatted as for specifier Z. 01:01
• C – calendar: the name or abbreviation of a calendar name
• E – era: the name of a baseline for the numbering of years, for example, the reign of a monarch

Sample:

• The expression `fn:`format-dateTime(\$dt, “[h].[m01][Pn] on [FNn], [D1o] [MNn]”) returns 3.58pm on Tuesday, 31st December
• The expression `fn:format-date(\$d, "[M]-[D]-[Y]")` returns 12-31-2002.
• The expression `fn:format-dateTime(\$dt, "[M01]/[D01]/[Y0001] at [H01]:[m01]:[s01]")` returns 12/31/2002 at 15:58:45.

## Format number

Returns a number in the specified format.

This function states that it returns the specified value as an integer.

Behind the scenes, this function is translated to the following XPath function: format-number(\$arg1, \$arg2)

• `fn:format-number`(`\$value as xs:numeric?``\$picture as xs:string`)` as xs:string`

Rules:

• Returns a string containing a number formatted according to a given picture string, taking account of decimal formats specified in the static context.
• The `\$arg1 `maybe of any numeric data type (xs:double, xs:float, xs:decimal, or their subtypes, including `xs:integer`).

Sample:

• The expression `format-number(12345.6, '#,###.00')` returns `"12,345.60"`.
• The expression `format-number(12345678.9, '9,999.99')` returns `"12,345,678.90"`.
• The expression `format-number(123.9, '9999')` returns `"0124"`.
• The expression `format-number(0.14, '01%')` returns `"14%"`.
• The expression `format-number(-6, '000')` returns `"-006"`.

## Format time

Returns a time in the specified format.

This function states that it returns the specified value as an integer.

Behind the scenes, this function is translated to the following XPath function: format-time(\$arg1, \$arg2)

• `fn:format-time`(`\$value as xs:time?``\$picture as xs:string`)` as xs:string?`

Rules:

• The `\$arg`1 needs to be an xs:time in the following format HH:mm:ssZ or HH:mm:ss-hh:mm.
• The `\$arg`2 (or picture) consists of a sequence of variable markers and literal substrings. A substring enclosed in square brackets is interpreted as a variable marker; substrings not enclosed in square brackets are taken as literal substrings. The literal substrings are optional and, if present, are rendered unchanged, including any whitespace. The variable markers are replaced in the result by strings representing aspects of the date and/or time to be formatted. These are described in detail below:
• H – hour in the day (24 hours)
• h – hour in half-day (12 hours)
• P – am/pm marker
• m – minute in the hour
• s – second in a minute
• f – fractional seconds
• Z – timezone
• z – timezone (same as Z, but modified where appropriate to include a prefix as a time offset using GMT, for example, GMT+1 or GMT-05:00. For this component, there is a fixed prefix of GMT or a localized variation thereof for the chosen language, and the remainder of the value is formatted as for specifier Z. 01:01
• C – calendar: the name or abbreviation of a calendar name
• E – era: the name of a baseline for the numbering of years, for example, the reign of a monarch

Sample:

• The expression `fn:`format-time(\$t, “[h]:[m01] [PN]”) returns 3:58 PM
• The expression `fn:format-time(\$t, "[h]:[m01]:[s01] [PN] [ZN,*-3]")` returns 3:58:45 PM PDT.
• The expression `fn:`format-time(\$t, “[h]:[m01]:[s01] o’clock [PN] [ZN,*-3]”) returns 3:58:45 o’clock PM PDT.
• The expression `fn:`format-time(\$t,”[H01]:[m01]:[s01] [z,6-6]”) returns 15:58:45 GMT+02:00.

Hope you find this helpful! So, if you liked the content or found it useful and want to help me write more, you can buy (or help buy) my son a Star Wars Lego!

##### Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc.

He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.

## Logic Apps (Standard) Data Mapper: Date and Time Functions (Part 5)

Because the Date and Time functions category has too many functions, I decide to break this blog post into different parts, so welcome to the fourth part!

## Overview

Date and Time functions are used to perform a variety of operations over Dates, such as retrieving the current date and time or adding dates, etc. If you come from the BizTalk Server background or are migrating BizTalk Server projects, they are the equivalent of Date/Time Functoids inside BizTalk Mapper Editor.

## Available Functions

The Date and Time functoids are:

• Add days: Adds a positive or negative number of days to the specified timestamp. Returns a timestamp that’s respectively later or earlier than the specified timestamp.
• Add DayTime to Date: Adds a positive or negative DayTime duration to the specified Date value (xs:date). Returns a Date that’s respectively after or before the specified Date.
• Add DayTime to DateTime: Adds a positive or negative DayTime duration to the specified DateTime value (xs:dateTime). Returns a DateTime that’s respectively after or before the specified DateTime.
• Add DayTime to Time: Adds a positive or negative DayTime duration to the specified Time value (xs:time). Returns a Time that’s respectively after or before the specified Time. Durations that wrap around past midnight also return an earlier Time.
• Add YearMonth to DateTime: Adds a positive or negative YearMonth duration to the specified DateTime value (xs:dateTime). Returns a DateTime that’s respectively after or before the specified DateTime.
• Adjust Date: Adjusts the specified Date value (xs:date) to the current or dynamic time zone.
• Adjust DateTime: Adjusts the specified DateTime value (xs:dateTime) to the current or dynamic time zone.
• Adjust Time: Adjusts the specified Time value (xs:time) to the current or dynamic time zone.
• Current date: Returns the current date in YYYY-MM-DD format.
• Current DateTime value: Returns the current date and time in YYYY-MM-DDThh:mm:ss format.
• Current time: Returns the current date and time in YYYY-MM-DDThh:mm:ss format.
• DateTime: Creates and returns a DateTime value based on the specified Date and Time.
• Day from Date: Returns the day from the specified Date value (xs:date).
• Day from DateTime: Returns the day from the specified DateTime value (xs:dateTime).
• Equal Date: Returns true or false based on whether the specified Date values are equal.
• Equal DateTime: Returns true or false based on whether with the specified DateTime values are equal.
• Equal Day: Returns true or false based on whether the specified Day values (xs:gDay) are equal with the same starting time when the day values are in the same month and year.
• Equal Month: Returns true or false based on whether the specified Month values (xs:gMonth) have the same starting time when the month values are in the same year.
• Equal MonthDay: Returns true or false based on whether the specified MonthDay values (xs:gMonthDay) are equal with the same starting time when the day values are in the same year.
• Equal Time: Returns true or false based on whether the specified Time values are equal.
• Equal Year: Returns true or false based on whether the specified Year values (xs:gYear) have the same starting time.
• Equal YearMonth: Returns true or false based on whether the specified YearMonth values (xs:gYearMonth) are the same.
• Greater Date: Returns true or false based on whether the first Date value is later than the second Date value.
• Greater DateTime: Returns true or false based on whether the first DateTime value is later than the second DateTime value.
• Greater Time: Returns true or false based on whether the first Time value is later than the second Time value.
• Hours from DateTime: Returns the hours from the specified DateTime value (xs:dateTime).
• Hours from Time: Returns the hours from the specified Time value (xs:time).
• Less Date: Returns true or false based on whether the first Date value is earlier than the second Date value.
• Less DateTime: Returns true or false based on whether the first DateTime value is earlier than the second DateTime value.
• Less Time: Returns true or false based on whether the first Time value is earlier than the second Time value.
• Minutes from DateTime: Returns the minutes from the specified DateTime value (xs:dateTime).
• Minutes from Time: Returns the minutes from the specified Time value (xs:time).
• Month from Date: Returns the month from the specified Date value (xs:date).
• Month from DateTime: Returns the month from the specified DateTime value (xs:dateTime).
• Seconds from DateTime: Returns the seconds from the specified DateTime value (xs:dateTime).
• Seconds from Time: Returns the seconds from the specified Time value (xs:time).
• Subtract Dates: Returns the DayTimeDuration value (xs:dayTimeDuration) representing the elapsed time between the starting times for the specified Date values.
• Subtract DateTimes: Returns a DayTimeDuration value (xs:dayTimeDuration) representing the elapsed time between the specified DateTime values..
• Subtract DateTime from Date: Subtracts a positive or negative DayTime duration from the specified Date value (xs:date). Returns a Date that’s respectively before or after the specified Date..
• Subtract DateTime from DateTime: Subtracts a positive or negative DayTime duration from the specified DateTime value (xs:dateTime). Returns a DateTime that’s respectively before or after the specified DateTime.
• Subtract DateTime from Time: Subtracts a positive or negative Time duration from the specified Time value (xs:time). Returns a Time that’s respectively before or after the specified Time. A duration that wraps around past midnight also returns a later Time.
• Subtract Times: Returns a DayTime: Duration value (xs:dayTimeDuration) representing the elapsed time between the specified Time values, which are treated as times on the same date.
• Subtract YearMonth from Date: Subtracts a positive or negative YearMonth duration from the specified Date value (xs:date). Returns a Date that’s respectively before or after the specified Date.
• Subtract YearMonth from DateTime: Subtracts a positive or negative YearMonth duration from the specified DateTime value (xs:dateTime). Returns a DateTime that’s respectively before or after the specified DateTime.
• Time zone from Date: Returns the time zone from the specified Date value (xs:date).
• Time zone from DateTime: Returns the time zone from the specified DateTime value (xs:dateTime).
• Time zone from Time: Returns the time zone from the specified Time value (xs:time).
• Year from Date: Returns the year from the specified Date value (xs:date).
• Year from DateTime: Returns the year from the specified DateTime value (xs:dateTime).

## Time zone from Date

This function states that it will return the time zone from the specified Date value (xs:date).

Behind the scenes, this function is translated to the following XPath function: timezone-from-date(\$arg)

• `fn:timezone-from-date`(`\$arg as xs:date?`)` as xs:dayTimeDuration?`

Rules:

• The function returns the timezone component of `\$arg`, if any. If `\$arg` has a timezone component, then the result is an `xs:dayTimeDuration` that indicates deviation from UTC; its value may range from +14:00 to -14:00 hours, both inclusive. If `\$arg` has no timezone component, the result is the empty sequence.
• The `\$arg`1 needs to be an xs:date in the following format yyyy-MM-DD-hh:mm or yyyy-MM-DDZ.

Sample:

• The expression `fn:timezone-from-date(xs:date("2023-07-29-05:00"))` returns `xs:dayTimeDuration("-PT5H")`.
• The expression `fn:timezone-from-date(xs:date("2023-07-29Z"))` returns `xs:dayTimeDuration("PT0S")`.
• The expression `fn:timezone-from-date(xs:date("2023-07-29"))` returns `()`.

## Time zone from DateTime

This function states that it will return the time zone from the specified DateTime value (xs:dateTime).

Behind the scenes, this function is translated to the following XPath function: timezone-from-dateTime(\$arg)

• `fn:timezone-from-dateTime`(`\$arg as xs:dateTime?`)` as xs:dayTimeDuration?`

Rules:

• The function returns the timezone component of `\$arg`, if any. If `\$arg` has a timezone component, the result indicates deviation from UTC; its value may range from +14:00 to -14:00 hours, both inclusive. If `\$arg` has no timezone component, the result is an empty sequence.
• The `\$arg`1 needs to be an xs:dateTime in the following format yyyy-MM-DDTHH:mm:ssZ or yyyy-MM-DDTHH:mm:ss-hh:mm.

Sample:

• The expression `fn:timezone-from-dateTime(xs:dateTime("2023-07-29T13:20:00-05:00"))` returns `xs:dayTimeDuration("-PT5H")`.
• The expression `fn:timezone-from-dateTime(xs:dateTime("2023-07-29T13:20:00Z"))` returns `xs:dayTimeDuration("PT0S")`.
• The expression `fn:timezone-from-dateTime(xs:dateTime("2023-07-29T00:00:00"))` returns `()`.

## Time zone from Time

This function states that it will return the time zone from the specified Time value (xs:time).

Behind the scenes, this function is translated to the following XPath function: timezone-from-time(\$arg)

• `fn:timezone-from-time`(`\$arg as xs:time?`)` as xs:dayTimeDuration?`

Rules:

• The function returns the timezone component of \$arg, if any. If \$arg it has a timezone component, the result indicates deviation from UTC; its value may range from +14:00 to -14:00 hours, both inclusive. If `\$arg` has no timezone component, the result is an empty sequence.
• The `\$arg` needs to be an xs:time in the following format HH:mm:ssZ or HH:mm:ss-hh:mm.

Sample:

• The expression `fn:timezone-from-time(xs:time("13:20:00-05:00"))` returns `xs:dayTimeDuration("-PT5H")`.
• The expression `fn:timezone-from-time(xs:time("13:20:00Z"))` returns `xs:dayTimeDuration("PT0S")`.
• The expression `fn:timezone-from-time(xs:time("13:20:00"))` returns `()`.

## Year from Date

This function states that it will return the year from the specified Date value (xs:date).

Behind the scenes, this function is translated to the following XPath function: year-from-date(\$arg)

• `fn:year-from-date`(`\$arg as xs:date?`)` as xs:integer?`

Rules:

• The function returns an `xs:integer` representing the year in the local value of `\$arg`. The value may be negative.
• The `\$arg` needs to be an xs:date in the following format yyyy-MM-DD or yyyy-MM-DDZ.

Sample:

• The expression `fn:year-from-date(xs:date("2023-07-29"))` returns `2023`.
• The expression `fn:year-from-date(xs:date("2023-07-29+05:00"))` returns `2023`.
• The expression `fn:year-from-date(xs:date("-0002-06-01"))` returns `-2`

## Year from DateTime

This function states that it will return the year from the specified DateTime value (xs:dateTime).

Behind the scenes, this function is translated to the following XPath function: year-from-dateTime(\$arg)

• `fn:year-from-dateTime`(`\$arg as xs:dateTime?`)` as xs:integer?`

Rules:

• The function returns an `xs:integer` representing the year in the local value of `\$arg`. The value may be negative.
• The `\$arg` needs to be an xs:dateTime in the following format yyyy-MM-DDTHH:mm:ss or yyyy-MM-DDTHH:mm:ss-hh:mm.

Sample:

• The expression `fn:year-from-dateTime(xs:dateTime("2023-07-29T21:30:00-05:00"))` returns `2023`.
• The expression `fn:year-from-dateTime(xs:dateTime("2023-07-29T19:20:00"))` returns `2023`.
• The expression `fn:year-from-dateTime(xs:dateTime("2021-12-31T24:00:00"))` returns `2022`.
• The expression `fn:year-from-dateTime(xs:dateTime("-0002-06-06T00:00:00"))` returns `-2`

Hope you find this helpful! So, if you liked the content or found it useful and want to help me write more, you can buy (or help buy) my son a Star Wars Lego!

##### Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc.

He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.

## Logic Apps (Standard) Data Mapper: Date and Time Functions (Part 4)

Because the Date and Time functions category has too many functions, I decide to break this blog post into different parts, so welcome to the fourth part!

## Overview

Date and Time functions are used to perform a variety of operations over Dates, such as retrieving the current date and time or adding dates, etc. If you come from the BizTalk Server background or are migrating BizTalk Server projects, they are the equivalent of Date/Time Functoids inside BizTalk Mapper Editor.

## Available Functions

The Date and Time functoids are:

• Add days: Adds a positive or negative number of days to the specified timestamp. Returns a timestamp that’s respectively later or earlier than the specified timestamp.
• Add DayTime to Date: Adds a positive or negative DayTime duration to the specified Date value (xs:date). Returns a Date that’s respectively after or before the specified Date.
• Add DayTime to DateTime: Adds a positive or negative DayTime duration to the specified DateTime value (xs:dateTime). Returns a DateTime that’s respectively after or before the specified DateTime.
• Add DayTime to Time: Adds a positive or negative DayTime duration to the specified Time value (xs:time). Returns a Time that’s respectively after or before the specified Time. Durations that wrap around past midnight also return an earlier Time.
• Add YearMonth to DateTime: Adds a positive or negative YearMonth duration to the specified DateTime value (xs:dateTime). Returns a DateTime that’s respectively after or before the specified DateTime.
• Adjust Date: Adjusts the specified Date value (xs:date) to the current or dynamic time zone.
• Adjust DateTime: Adjusts the specified DateTime value (xs:dateTime) to the current or dynamic time zone.
• Adjust Time: Adjusts the specified Time value (xs:time) to the current or dynamic time zone.
• Current date: Returns the current date in YYYY-MM-DD format.
• Current DateTime value: Returns the current date and time in YYYY-MM-DDThh:mm:ss format.
• Current time: Returns the current date and time in YYYY-MM-DDThh:mm:ss format.
• DateTime: Creates and returns a DateTime value based on the specified Date and Time.
• Day from Date: Returns the day from the specified Date value (xs:date).
• Day from DateTime: Returns the day from the specified DateTime value (xs:dateTime).
• Equal Date: Returns true or false based on whether the specified Date values are equal.
• Equal DateTime: Returns true or false based on whether with the specified DateTime values are equal.
• Equal Day: Returns true or false based on whether the specified Day values (xs:gDay) are equal with the same starting time when the day values are in the same month and year.
• Equal Month: Returns true or false based on whether the specified Month values (xs:gMonth) have the same starting time when the month values are in the same year.
• Equal MonthDay: Returns true or false based on whether the specified MonthDay values (xs:gMonthDay) are equal with the same starting time when the day values are in the same year.
• Equal Time: Returns true or false based on whether the specified Time values are equal.
• Equal Year: Returns true or false based on whether the specified Year values (xs:gYear) have the same starting time.
• Equal YearMonth: Returns true or false based on whether the specified YearMonth values (xs:gYearMonth) are the same.
• Greater Date: Returns true or false based on whether the first Date value is later than the second Date value.
• Greater DateTime: Returns true or false based on whether the first DateTime value is later than the second DateTime value.
• Greater Time: Returns true or false based on whether the first Time value is later than the second Time value.
• Hours from DateTime: Returns the hours from the specified DateTime value (xs:dateTime).
• Hours from Time: Returns the hours from the specified Time value (xs:time).
• Less Date: Returns true or false based on whether the first Date value is earlier than the second Date value.
• Less DateTime: Returns true or false based on whether the first DateTime value is earlier than the second DateTime value.
• Less Time: Returns true or false based on whether the first Time value is earlier than the second Time value.
• Minutes from DateTime: Returns the minutes from the specified DateTime value (xs:dateTime).
• Minutes from Time: Returns the minutes from the specified Time value (xs:time).
• Month from Date: Returns the month from the specified Date value (xs:date).
• Month from DateTime: Returns the month from the specified DateTime value (xs:dateTime).
• Seconds from DateTime: Returns the seconds from the specified DateTime value (xs:dateTime).
• Seconds from Time: Returns the seconds from the specified Time value (xs:time).
• Subtract Dates: Returns the DayTimeDuration value (xs:dayTimeDuration) representing the elapsed time between the starting times for the specified Date values.
• Subtract DateTimes: Returns a DayTimeDuration value (xs:dayTimeDuration) representing the elapsed time between the specified DateTime values..
• Subtract DateTime from Date: Subtracts a positive or negative DayTime duration from the specified Date value (xs:date). Returns a Date that’s respectively before or after the specified Date..
• Subtract DateTime from DateTime: Subtracts a positive or negative DayTime duration from the specified DateTime value (xs:dateTime). Returns a DateTime that’s respectively before or after the specified DateTime.
• Subtract DateTime from Time: Subtracts a positive or negative Time duration from the specified Time value (xs:time). Returns a Time that’s respectively before or after the specified Time. A duration that wraps around past midnight also returns a later Time.
• Subtract Times: Returns a DayTimeDuration value (xs:dayTimeDuration) representing the elapsed time between the specified Time values, which are treated as times on the same date.
• Subtract YearMonth from Date: Subtracts a positive or negative YearMonth duration from the specified Date value (xs:date). Returns a Date that’s respectively before or after the specified Date.
• Subtract YearMonth from DateTime: Subtracts a positive or negative YearMonth duration from the specified DateTime value (xs:dateTime). Returns a DateTime that’s respectively before or after the specified DateTime.
• Time zone from Date: Returns the time zone from the specified Date value (xs:date).
• Time zone from DateTime: Returns the time zone from the specified DateTime value (xs:dateTime).
• Time zone from Time: Returns the time zone from the specified Time value (xs:time).
• Year from Date: Returns the year from the specified Date value (xs:date).
• Year from DateTime: Returns the year from the specified DateTime value (xs:dateTime).

## Seconds from DateTime

This function states that it will return the seconds from the specified DateTime value (xs:dateTime).

Behind the scenes, this function is translated to the following XPath function: seconds-from-dateTime(\$ard)

• `fn:seconds-from-dateTime`(`\$arg as xs:dateTime?`)` as xs:decimal?`

Rules:

• The function returns an `xs:decimal` value greater than or equal to zero and less than 60, representing the seconds and fractional seconds defined in `\$arg` without adjusting the timezone.
• The `\$arg` needs to be an xs:dateTime in the following format yyyy-MM-DDTHH:mm:ss or yyyy-MM-DDTHH:mm:ss-hh:mm.

Sample:

• The expression `fn:seconds-from-dateTime(xs:dateTime("1999-05-31T13:20:32-05:00"))` returns `32`.

## Seconds from Time

This function states that it will return the seconds from the specified Time value (xs:time).

Behind the scenes, this function is translated to the following XPath function: seconds-from-time(\$arg)

• `fn:seconds-from-time`(`\$arg as xs:time?`)` as xs:decimal?`

Rules:

• the function returns an `xs:decimal` value greater than or equal to zero and less than 60, representing the seconds and fractional seconds defined in `\$arg` without adjusting the timezone.
• The `\$arg` needs to be an xs:time in the following format HH:mm:ss or HH:mm:ss-hh:mm.

Sample:

• The expression `fn:seconds-from-time(xs:time("13:20:10.5"))` returns `10.5`.

## Subtract Dates

This function states that it will return the DayTimeDuration value (xs:dayTimeDuration) representing the elapsed time between the starting times for the specified Date values.

Behind the scenes, this function is translated to the following XPath expression: xs:dayTimeDuration(xs:date(\$arg1) – xs:date(\$arg2))

• xs:dayTimeDuration(xs:date(\$arg1) – xs:date(\$arg2)) `as xs:dayTimeDuration`

Rules:

• If the starting instant of `\$arg1` precedes in time the starting instant of `\$arg2`, then the returned value is a negative duration.
• Returns the `xs:dayTimeDuration` (for example, “PT2H12M”) that corresponds to the elapsed time between the starting instant of `\$arg1` and the starting instant of `\$arg2`.
• The `\$arg`1 and \$arg2 need to be an xs:date in the following format yyyy-MM-DD or yyyy-MM-DDZ.

Sample:

• The following Data Mapper transformation rule: subtract-dates(“2023-07-28”, “1978-04-04”) will be translated to {xs:dayTimeDuration(xs:date(‘2023-07-28’) – xs:date(‘1978-04-04’))} and the return will be P16551D.

## Subtract DateTimes

This function states that it will return a DayTimeDuration value (xs:dayTimeDuration) representing the elapsed time between the specified DateTime values.

Behind the scenes, this function is translated to the following XPath expression: xs:dayTimeDuration(xs:dateTime(\$arg1) – xs:dateTime(\$arg2))

• xs:dayTimeDuration(xs:dateTime(\$arg1) – xs:dateTime(\$arg2)) `as xs:dayTimeDuration`

Rules:

• If the normalized value of `\$arg1` precedes in time the normalized value of `\$arg2`, then the returned value is a negative duration.
• Returns an `xs:dayTimeDuration` representing the amount of elapsed time between the instants `arg2` and `arg1`.
• The `\$arg`1 and \$arg2 need to be an xs:dateTime in the following format yyyy-MM-DDTHH:mm:ss or yyyy-MM-DDTHH:mm:ss-hh:mm.

Sample:

• The following Data Mapper transformation rule: subtract-datetimes(“2023-07-28T23:00:00”, “1978-04-04T10:30:00”) will be translated to {xs:dayTimeDuration(xs:dateTime(‘2023-07-28T23:00:00’) – xs:dateTime(‘1978-04-04T10:30:00’))} and the return will be P16551DT12H30M.

## Subtract DateTime from Date

This function states that it will subtract a positive or negative DayTime duration from the specified Date value (xs:date). Returns a Date that’s respectively before or after the specified Date.

Behind the scenes, this function is translated to the following XPath expression: xs:date(xs:date(\$arg1) – xs:dayTimeDuration(\$arg2))

• xs:date(xs:date(\$arg1) – xs:dayTimeDuration(\$arg2)) as xs:date

Rules:

• Returns the `xs:date` that is a given duration before a specified `xs:date` (or after, if the duration is negative).
• The `\$arg`1 needs to be an xs:date in the following format yyyy-MM-DD or yyyy-MM-DDZ.
• The `\$arg`1 needs to be an xs:dayTimeDuration in the ISO 8601 format. ISO-8601 standard was created to elaborate crystal clear language to talk about dates and periods and avoid misinterpretation. Where Duration (“how long“) is represented with the format P[n]Y[n]M[n]DT[n]H[n]M[n]S, where n is a number.

Sample:

• The following Data Mapper transformation rule: subtract-daytime-from-date(“2023-07-28”, “P16551D”) will be translated to {xs:date(xs:date(‘2023-07-28’) – xs:dayTimeDuration(‘P16551D’))} and the return will be 1978-04-04.

## Subtract DateTime from DateTime

This function states that it will subtract a positive or negative DayTime duration from the specified DateTime value (xs:dateTime). Returns a DateTime that’s respectively before or after the specified DateTime.

Behind the scenes, this function is translated to the following XPath expression: xs:dateTime(xs:dateTime(\$arg1) – xs:dayTimeDuration(\$arg2))

• xs:dateTime(xs:dateTime(\$arg1) – xs:dayTimeDuration(\$arg2)) as xs:dateTime

Rules:

• Returns the `xs:dateTime` that is a given duration before a specified `xs:dateTime` (or after, if the duration is negative).
• The `\$arg`1 needs to be an xs:dateTime in the following format yyyy-MM-DDTHH:mm:ss or yyyy-MM-DDTHH:mm:ss-hh:mm.
• The `\$arg`1 needs to be an xs:dayTimeDuration in the ISO 8601 format. ISO-8601 standard was created to elaborate crystal clear language to talk about dates and periods and avoid misinterpretation. Where Duration (“how long“) is represented with the format P[n]Y[n]M[n]DT[n]H[n]M[n]S, where n is a number.

Sample:

• The following Data Mapper transformation rule: subtract-daytime-from-datetime(“2023-07-28T23:00:00”, “P16551DT12H30M”) will be translated to {xs:dateTime(xs:dateTime(‘2023-07-28T23:00:00’) – xs:dayTimeDuration(‘P16551DT12H30M’))} and the return will be 1978-04-04T10:30:00.

## Subtract DateTime from Time

This function states that it will subtract a positive or negative Time duration from the specified Time value (xs:time). Returns a Time that’s respectively before or after the specified Time. A duration that wraps around past midnight also returns a later Time.

Behind the scenes, this function is translated to the following XPath expression: xs:time(xs:time(\$arg1) – xs:dayTimeDuration(\$arg2))

• xs:time(xs:time(\$arg1) – xs:dayTimeDuration(\$arg2)) as xs:time

Rules:

• Returns the `xs:time` value that is a given duration before a specified `xs:time` (or after, if the duration is negative or causes wrap-around past midnight)
• The `\$arg`1 needs to be an xs:time in the following format HH:mm:ss or HH:mm:ss-hh:mm.
• The `\$arg`1 needs to be an xs:dayTimeDuration in the ISO 8601 format. ISO-8601 standard was created to elaborate crystal clear language to talk about dates and periods and avoid misinterpretation. Where Duration (“how long“) is represented with the format P[n]Y[n]M[n]DT[n]H[n]M[n]S, where n is a number.

Sample:

• The following Data Mapper transformation rule: subtract-daytime-from-time(“11:00:00”, “PT30M”) will be translated to {xs:time(xs:time(’11:00:00?) – xs:dayTimeDuration(‘PT30M’))} and the return will be 10:30:00.

## Subtract Times

This function states that it will return a DayTimeDuration value (xs:dayTimeDuration) representing the elapsed time between the specified Time values, which are treated as times on the same date.

Behind the scenes, this function is translated to the following XPath expression: xs:dayTimeDuration(xs:time(\$arg1) – xs:time(\$arg2))

• xs:dayTimeDuration(xs:time(\$arg1) – xs:time(\$arg2)) as xs:dayTimeDuration

Rules:

• Returns the `xs:dayTimeDuration` that corresponds to the elapsed time between the values of `\$arg2` and `\$arg1` treated as times on the same date.
• The `\$arg`1 and \$arg2 need to be an xs:time in the following format HH:mm:ss or HH:mm:ss-hh:mm.

Sample:

• The following Data Mapper transformation rule: subtract-times(“10:30:00”, “10:00:00”) will be translated to {xs:dayTimeDuration(xs:time(’10:30:00?) – xs:time(’10:00:00?))} and the return will be PT30M.

## Subtract YearMonth from Date

This function states that it will subtract a positive or negative YearMonth duration from the specified Date value (xs:date). Returns a Date that’s respectively before or after the specified Date.

Behind the scenes, this function is translated to the following XPath expression: xs:date(xs:date(\$arg1) – xs:yearMonthDuration(\$arg2))

• xs:date(xs:date(\$arg1) – xs:yearMonthDuration(\$arg2)) as xs:date

Rules:

• Returns the `xs:date` that is a given duration after a specified `xs:date` (or before, if the duration is negative).
• The `\$arg`1 needs to be an xs:date in the following format yyyy-MM-DD or yyyy-MM-DDZ.
• The `\$arg`2 needs to be an interval in ISO 8601 format. ISO-8601 standard was created to elaborate crystal clear language to discuss dates and periods and avoid misinterpretation. Where Duration (“how long“) is represented with the format P[n]Y[n], where n is a number – xs:yearMonthDuration.

Sample:

• The following Data Mapper transformation rule: subtract-yearmonth-from-date(“2023-07-28”, “P1Y”) will be translated to {xs:date(xs:date(‘2023-07-28’) – xs:yearMonthDuration(‘P1Y’))} and the return will be 2022-07-28.

## Subtract YearMonth from DateTime

This function states that it will subtract a positive or negative YearMonth duration from the specified DateTime value (xs:dateTime). Returns a DateTime that’s respectively before or after the specified DateTime.

Behind the scenes, this function is translated to the following XPath expression: xs:dateTime(xs:dateTime(\$arg1) – xs:yearMonthDuration(\$arg2)

• xs:dateTime(xs:dateTime(\$arg1) – xs:yearMonthDuration(\$arg2) as xs:dateTime

Rules:

• Returns the `xs:dateTime` that is a given duration before a specified `xs:dateTime` (or after, if the duration is negative).
• The `\$arg`1 needs to be an xs:dateTime in the following format yyyy-MM-DDTHH:mm:ss or yyyy-MM-DDTHH:mm:ss-hh:mm.
• The `\$arg`2 needs to be an interval in ISO 8601 format. ISO-8601 standard was created to elaborate crystal clear language to discuss dates and periods and avoid misinterpretation. Where Duration (“how long“) is represented with the format P[n]Y[n], where n is a number – xs:yearMonthDuration.

Sample:

• The following Data Mapper transformation rule: subtract-yearmonth-from-datetime(“2023-07-28T12:00:00”, “P1Y”) will be translated to {xs:dateTime(xs:dateTime(‘2023-07-28T12:00:00’) – xs:yearMonthDuration(‘P1Y’))} and the return will be 2022-07-28T12:00:00.

Stay tuned for the fifth part of this blog post.

Hope you find this helpful! So, if you liked the content or found it useful and want to help me write more, you can buy (or help buy) my son a Star Wars Lego!

##### Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc.

He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.

## Logic Apps (Standard) Data Mapper: Date and Time Functions (Part 3)

Because the Date and Time functions category has too many functions, I decide to break this blog post into different parts, so welcome to the third part!

## Overview

Date and Time functions are used to perform a variety of operations over Dates, such as retrieving the current date and time or adding dates, etc. If you come from the BizTalk Server background or are migrating BizTalk Server projects, they are the equivalent of Date/Time Functoids inside BizTalk Mapper Editor.

## Available Functions

The Date and Time functoids are:

• Add days: Adds a positive or negative number of days to the specified timestamp. Returns a timestamp that’s respectively later or earlier than the specified timestamp.
• Add DayTime to Date: Adds a positive or negative DayTime duration to the specified Date value (xs:date). Returns a Date that’s respectively after or before the specified Date.
• Add DayTime to DateTime: Adds a positive or negative DayTime duration to the specified DateTime value (xs:dateTime). Returns a DateTime that’s respectively after or before the specified DateTime.
• Add DayTime to Time: Adds a positive or negative DayTime duration to the specified Time value (xs:time). Returns a Time that’s respectively after or before the specified Time. Durations that wrap around past midnight also return an earlier Time.
• Add YearMonth to DateTime: Adds a positive or negative YearMonth duration to the specified DateTime value (xs:dateTime). Returns a DateTime that’s respectively after or before the specified DateTime.
• Adjust Date: Adjusts the specified Date value (xs:date) to the current or dynamic time zone.
• Adjust DateTime: Adjusts the specified DateTime value (xs:dateTime) to the current or dynamic time zone.
• Adjust Time: Adjusts the specified Time value (xs:time) to the current or dynamic time zone.
• Current date: Returns the current date in YYYY-MM-DD format.
• Current DateTime value: Returns the current date and time in YYYY-MM-DDThh:mm:ss format.
• Current time: Returns the current date and time in YYYY-MM-DDThh:mm:ss format.
• DateTime: Creates and returns a DateTime value based on the specified Date and Time.
• Day from Date: Returns the day from the specified Date value (xs:date).
• Day from DateTime: Returns the day from the specified DateTime value (xs:dateTime).
• Equal Date: Returns true or false based on whether the specified Date values are equal.
• Equal DateTime: Returns true or false based on whether with the specified DateTime values are equal.
• Equal Day: Returns true or false based on whether the specified Day values (xs:gDay) are equal with the same starting time when the day values are in the same month and year.
• Equal Month: Returns true or false based on whether the specified Month values (xs:gMonth) have the same starting time when the month values are in the same year.
• Equal MonthDay: Returns true or false based on whether the specified MonthDay values (xs:gMonthDay) are equal with the same starting time when the day values are in the same year.
• Equal Time: Returns true or false based on whether the specified Time values are equal.
• Equal Year: Returns true or false based on whether the specified Year values (xs:gYear) have the same starting time.
• Equal YearMonth: Returns true or false based on whether the specified YearMonth values (xs:gYearMonth) are the same.
• Greater Date: Returns true or false based on whether the first Date value is later than the second Date value.
• Greater DateTime: Returns true or false based on whether the first DateTime value is later than the second DateTime value.
• Greater Time: Returns true or false based on whether the first Time value is later than the second Time value.
• Hours from DateTime: Returns the hours from the specified DateTime value (xs:dateTime).
• Hours from Time: Returns the hours from the specified Time value (xs:time).
• Less Date: Returns true or false based on whether the first Date value is earlier than the second Date value.
• Less DateTime: Returns true or false based on whether the first DateTime value is earlier than the second DateTime value.
• Less Time: Returns true or false based on whether the first Time value is earlier than the second Time value.
• Minutes from DateTime: Returns the minutes from the specified DateTime value (xs:dateTime).
• Minutes from Time: Returns the minutes from the specified Time value (xs:time).
• Month from Date: Returns the month from the specified Date value (xs:date).
• Month from DateTime: Returns the month from the specified DateTime value (xs:dateTime).
• Seconds from DateTime: Returns the seconds from the specified DateTime value (xs:dateTime).
• Seconds from Time: Returns the seconds from the specified Time value (xs:time).
• Subtract Dates: Returns the DayTimeDuration value (xs:dayTimeDuration) representing the elapsed time between the starting times for the specified Date values.
• Subtract DateTimes: Returns a DayTimeDuration value (xs:dayTimeDuration) representing the elapsed time between the specified DateTime values..
• Subtract DateTime from Date: Subtracts a positive or negative DayTime duration from the specified Date value (xs:date). Returns a Date that’s respectively before or after the specified Date..
• Subtract DateTime from DateTime: Subtracts a positive or negative DayTime duration from the specified DateTime value (xs:dateTime). Returns a DateTime that’s respectively before or after the specified DateTime.
• Subtract DateTime from Time: Subtracts a positive or negative Time duration from the specified Time value (xs:time). Returns a Time that’s respectively before or after the specified Time. A duration that wraps around past midnight also returns a later Time.
• Subtract Times: Returns a DayTimeDuration value (xs:dayTimeDuration) representing the elapsed time between the specified Time values, which are treated as times on the same date.
• Subtract YearMonth from Date: Subtracts a positive or negative YearMonth duration from the specified Date value (xs:date). Returns a Date that’s respectively before or after the specified Date.
• Subtract YearMonth from DateTime: Subtracts a positive or negative YearMonth duration from the specified DateTime value (xs:dateTime). Returns a DateTime that’s respectively before or after the specified DateTime.
• Time zone from Date: Returns the time zone from the specified Date value (xs:date).
• Time zone from DateTime: Returns the time zone from the specified DateTime value (xs:dateTime).
• Time zone from Time: Returns the time zone from the specified Time value (xs:time).
• Year from Date: Returns the year from the specified Date value (xs:date).
• Year from DateTime: Returns the year from the specified DateTime value (xs:dateTime).

## Greater Date

This function states that it will return true or false based on whether the first Date value is later than the second Date value.

Behind the scenes, this function is translated to the following XPath expression: xs:date(\$arg1) > xs:date(\$arg2)

• xs:date(\$arg1) > xs:date(\$arg2) as xs:boolean

Rules:

• Returns `true` if and only if the starting instant of `\$arg1` is greater than the starting instant of `\$arg2`. Returns `false` otherwise.
• The `\$arg`1 and \$arg2 need to be an xs:date in the following format yyyy-MM-DD or yyyy-MM-DDZ.

Sample:

• The following Data Mapper transformation rule: is-greater-than-date(“2013-07-28”, “2013-07-29”) will be translated to {xs:date(‘2013-07-28’) > xs:date(‘2013-07-29’)} and the return will be false.

## Greater DateTime

This function states that it will return true or false based on whether the first DateTime value is later than the second DateTime value.

Behind the scenes, this function is translated to the following XPath expression: xs:dateTime(\$arg1) > xs:dateTime(\$arg2)

• xs:dateTime(\$arg1) > xs:dateTime(\$arg2) as xs:boolean

Rules:

• Returns `true` if the first argument represents a later instant in time than the second argument. Returns `false` otherwise.
• The `\$arg`1 and \$arg2 need to be an xs:dateTime in the following format yyyy-MM-DDTHH:mm:ss or yyyy-MM-DDTHH:mm:ss-hh:mm.

Sample:

• The following Data Mapper transformation rule: is-greater-than-datetime(“2023-07-25T12:00:00+01:00”, “2023-07-25T13:00:00+01:00”) will be translated to {xs:dateTime(‘2023-07-25T12:00:00+01:00’) > xs:dateTime(‘2023-07-25T13:00:00+01:00’)} and the return will be false.

## Greater Time

This function states that it will return true or false based on whether the first Time value is later than the second Time value.

Behind the scenes, this function is translated to the following XPath expression: xs:time(\$arg1) > xs:time(\$arg2)

• xs:time(\$arg1) > xs:time(\$arg2) as xs:boolean

Rules:

• Returns `true` if the first `xs:time` value represents a later instant in time than the second, when both are treated as being times on the same date, before adjusting the timezone.
• The `\$arg1 and \$arg2 need` to be an xs:time in the following format HH:mm:ss or HH:mm:ss-hh:mm.

Sample:

• The following Data Mapper transformation rule: is-greater-than-time(“01:00:00”, “02:00:00”) will be translated to {xs:time(’01:00:00?) > xs:time(’02:00:00?)} and the return will be false.

## Hours from DateTime

This function states that it will return the hours from the specified DateTime value (xs:dateTime).

Behind the scenes, this function is translated to the following XPath function: hours-from-dateTime(\$arg1)

• `fn:hours-from-dateTime`(`\$arg as xs:dateTime?`)` as xs:integer?`

Rules:

• Returns the hours component of an `xs:dateTime` without adjusting the timezone.
• The `\$arg`1 needs to be an xs:dateTime in the following format yyyy-MM-DDTHH:mm:ss or yyyy-MM-DDTHH:mm:ss-hh:mm.

Sample:

• The expression `fn:hours-from-dateTime(xs:dateTime("1999-05-31T08:20:00-05:00"))` returns `8`.

## Hours from Time

This function states that it will return the hours from the specified Time value (xs:time).

Behind the scenes, this function is translated to the following XPath function: hours-from-time(\$arg1)

• `fn:hours-from-time`(`\$arg as xs:time?`)` as xs:integer?`

Rules:

• Returns the hours component of an `xs:time` without adjusting the timezone.
• The `\$arg`1 needs to be an xs:time in the following format HH:mm:ss or HH:mm:ss-hh:mm.

Sample:

• The following Data Mapper transformation rule: is-less-than-datetime(“2023-07-25T12:00:00+01:00”, “2023-07-25T13:00:00+01:00”) will be translated to {xs:dateTime(‘2023-07-25T12:00:00+01:00’) > xs:dateTime(‘2023-07-25T13:00:00+01:00’)} and the return will be false.

## Less Date

This function states that it will return true or false based on whether the first Date value is earlier than the second Date value.

Behind the scenes, this function is translated to the following XPath expression: xs:date(\$arg1) < xs:date(\$arg2)

• xs:date(\$arg1) < xs:date(\$arg2) as xs:boolean

Rules:

• Returns `true` if and only if the starting instant of `\$arg1` is less than the starting instant of `\$arg2`. Returns `false` otherwise.
• The `\$arg`1 and \$arg2 need to be an xs:date in the following format yyyy-MM-DD or yyyy-MM-DDZ.

Sample:

• The following Data Mapper transformation rule: is-less-than-date(“2023-07-28”, “2023-07-29”) will be translated to {xs:date(‘2023-07-28’) < xs:date(‘2023-07-29’)} and the return will be true.

## Less DateTime

This function states that it will return true or false based on whether the first DateTime value is earlier than the second DateTime value.

Behind the scenes, this function is translated to the following XPath function: xs:dateTime(\$arg1) < xs:dateTime(\$arg2)

• xs:dateTime(\$arg1) < xs:dateTime(\$arg2) as xs:boolean

Rules:

• Returns `true` if the first argument represents an earlier instant in time than the second argument.
• The `\$arg`1 and \$arg2 need to be an xs:dateTime in the following format yyyy-MM-DDTHH:mm:ss or yyyy-MM-DDTHH:mm:ss-hh:mm.

Sample:

• The following Data Mapper transformation rule: is-less-than-datetime(“2023-07-25T12:00:00+01:00”, “2023-07-25T13:00:00+01:00”) will be translated to {xs:dateTime(‘2023-07-25T12:00:00+01:00’) < xs:dateTime(‘2023-07-25T13:00:00+01:00’)} and the return will be true.

## Less Time

This function states that it will return true or false based on whether the first Time value is earlier than the second Time value.

Behind the scenes, this function is translated to the following XPath function: xs:time(\$arg1) < xs:time(\$arg2)

• xs:time(\$arg1) < xs:time(\$arg2) as xs:boolean

Rules:

• Returns `true` if the first `xs:time` value represents an earlier instant in time than the second, when both are treated as being times on the same date, before adjusting the timezone.
• The `\$arg1 and \$arg2 need` to be an xs:time in the following format HH:mm:ss or HH:mm:ss-hh:mm.

Sample:

• The following Data Mapper transformation rule: is-less-than-time(“12:00:00”, “13:00:00”) will be translated to {xs:time(’12:00:00?) < xs:time(’13:00:00?)} and the return will be true.

## Minutes from DateTime

This function states that it will return the minutes from the specified DateTime value (xs:dateTime).

Behind the scenes, this function is translated to the following XPath function: minutes-from-dateTime(\$arg)

• `fn:minutes-from-dateTime`(`\$arg as xs:dateTime?`)` as xs:integer?`

Rules:

• The function returns an `xs:integer` value between 0 and 59, both inclusive, representing the minute component defined in the `\$arg` without adjusting the timezone.
• The `\$arg` needs to be an xs:dateTime in the following format yyyy-MM-DDTHH:mm:ss or yyyy-MM-DDTHH:mm:ss-hh:mm.

Sample:

• The expression `fn:minutes-from-dateTime(xs:dateTime("1999-05-31T13:30:00+05:30"))` returns `30`.

## Minutes from Time

This function states that it will return the minutes from the specified Time value (xs:time).

Behind the scenes, this function is translated to the following XPath function: minutes-from-time(\$arg)

• number(\$arg as xs:anyAtomicType?) as xs:boolean

Rules:

• Returns the minutes component of an `xs:time` without adjusting the timezone.
• The `\$arg` needs to be an xs:time in the following format HH:mm:ss or HH:mm:ss-hh:mm.

Sample:

• The expression `fn:minutes-from-time(xs:time("13:00:00Z"))` returns `0`.

## Month from Date

This function states that it will return the month from the specified Date value (xs:date).

Behind the scenes, this function is translated to the following XPath function: month-from-date(\$arg)

• `fn:month-from-date`(`\$arg as xs:date?`)` as xs:integer?`

Rules:

• The function returns an `xs:integer` between 1 and 12, both inclusive, representing the month component in the local value of `\$arg`.
• The `\$arg` needs to be an xs:date in the following format yyyy-MM-DD or yyyy-MM-DDZ.

Sample:

• The expression `fn:month-from-date(xs:date("1999-05-31"))` returns `5`

## Month from DateTime

This function states that it will return the month from the specified DateTime value (xs:dateTime).

Behind the scenes, this function is translated to the following XPath function: month-from-dateTime(\$arg)

• number(\$arg as xs:anyAtomicType?) as xs:boolean

Rules:

• The function returns an `xs:integer` between 1 and 12, both inclusive, representing the month defined on the `\$arg without adjusting the timezone`.
• The `\$arg` needs to be an xs:dateTime in the following format yyyy-MM-DDTHH:mm:ss or yyyy-MM-DDTHH:mm:ss-hh:mm.

Sample:

• The expression `fn:month-from-dateTime(xs:dateTime("1999-05-31T13:20:00-05:00"))` returns `5`.

Stay tuned for the fourth part of this blog post.

Hope you find this helpful! So, if you liked the content or found it useful and want to help me write more, you can buy (or help buy) my son a Star Wars Lego!

##### Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc.

He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.

## Logic Apps (Standard) Data Mapper: Date and Time Functions (Part 2)

Because the Date and Time functions category has too many functions, I decide to break this blog post into different parts, so welcome to the second part!

## Overview

Date and Time functions are used to perform a variety of operations over Dates, such as retrieving the current date and time or adding dates, etc. If you come from the BizTalk Server background or are migrating BizTalk Server projects, they are the equivalent of Date/Time Functoids inside BizTalk Mapper Editor.

## Available Functions

The Date and Time functoids are:

• Add days: Adds a positive or negative number of days to the specified timestamp. Returns a timestamp that’s respectively later or earlier than the specified timestamp.
• Add DayTime to Date: Adds a positive or negative DayTime duration to the specified Date value (xs:date). Returns a Date that’s respectively after or before the specified Date.
• Add DayTime to DateTime: Adds a positive or negative DayTime duration to the specified DateTime value (xs:dateTime). Returns a DateTime that’s respectively after or before the specified DateTime.
• Add DayTime to Time: Adds a positive or negative DayTime duration to the specified Time value (xs:time). Returns a Time that’s respectively after or before the specified Time. Durations that wrap around past midnight also return an earlier Time.
• Add YearMonth to DateTime: Adds a positive or negative YearMonth duration to the specified DateTime value (xs:dateTime). Returns a DateTime that’s respectively after or before the specified DateTime.
• Adjust Date: Adjusts the specified Date value (xs:date) to the current or dynamic time zone.
• Adjust DateTime: Adjusts the specified DateTime value (xs:dateTime) to the current or dynamic time zone.
• Adjust Time: Adjusts the specified Time value (xs:time) to the current or dynamic time zone.
• Current date: Returns the current date in YYYY-MM-DD format.
• Current DateTime value: Returns the current date and time in YYYY-MM-DDThh:mm:ss format.
• Current time: Returns the current date and time in YYYY-MM-DDThh:mm:ss format.
• DateTime: Creates and returns a DateTime value based on the specified Date and Time.
• Day from Date: Returns the day from the specified Date value (xs:date).
• Day from DateTime: Returns the day from the specified DateTime value (xs:dateTime).
• Equal Date: Returns true or false based on whether the specified Date values are equal.
• Equal DateTime: Returns true or false based on whether with the specified DateTime values are equal.
• Equal Day: Returns true or false based on whether the specified Day values (xs:gDay) are equal with the same starting time when the day values are in the same month and year.
• Equal Month: Returns true or false based on whether the specified Month values (xs:gMonth) have the same starting time when the month values are in the same year.
• Equal MonthDay: Returns true or false based on whether the specified MonthDay values (xs:gMonthDay) are equal with the same starting time when the day values are in the same year.
• Equal Time: Returns true or false based on whether the specified Time values are equal.
• Equal Year: Returns true or false based on whether the specified Year values (xs:gYear) have the same starting time.
• Equal YearMonth: Returns true or false based on whether the specified YearMonth values (xs:gYearMonth) are the same.
• Greater Date: Returns true or false based on whether the first Date value is later than the second Date value.
• Greater DateTime: Returns true or false based on whether the first DateTime value is later than the second DateTime value.
• Greater Time: Returns true or false based on whether the first Time value is later than the second Time value.
• Hours from DateTime: Returns the hours from the specified DateTime value (xs:dateTime).
• Hours from Time: Returns the hours from the specified Time value (xs:time).
• Less Date: Returns true or false based on whether the first Date value is earlier than the second Date value.
• Less DateTime: Returns true or false based on whether the first DateTime value is earlier than the second DateTime value.
• Less Time: Returns true or false based on whether the first Time value is earlier than the second Time value.
• Minutes from DateTime: Returns the minutes from the specified DateTime value (xs:dateTime).
• Minutes from Time: Returns the minutes from the specified Time value (xs:time).
• Month from Date: Returns the month from the specified Date value (xs:date).
• Month from DateTime: Returns the month from the specified DateTime value (xs:dateTime).
• Seconds from DateTime: Returns the seconds from the specified DateTime value (xs:dateTime).
• Seconds from Time: Returns the seconds from the specified Time value (xs:time).
• Subtract Dates: Returns the DayTimeDuration value (xs:dayTimeDuration) representing the elapsed time between the starting times for the specified Date values.
• Subtract DateTimes: Returns a DayTimeDuration value (xs:dayTimeDuration) representing the elapsed time between the specified DateTime values..
• Subtract DateTime from Date: Subtracts a positive or negative DayTime duration from the specified Date value (xs:date). Returns a Date that’s respectively before or after the specified Date..
• Subtract DateTime from DateTime: Subtracts a positive or negative DayTime duration from the specified DateTime value (xs:dateTime). Returns a DateTime that’s respectively before or after the specified DateTime.
• Subtract DateTime from Time: Subtracts a positive or negative Time duration from the specified Time value (xs:time). Returns a Time that’s respectively before or after the specified Time. A duration that wraps around past midnight also returns a later Time.
• Subtract Times: Returns a DayTimeDuration value (xs:dayTimeDuration) representing the elapsed time between the specified Time values, which are treated as times on the same date.
• Subtract YearMonth from Date: Subtracts a positive or negative YearMonth duration from the specified Date value (xs:date). Returns a Date that’s respectively before or after the specified Date.
• Subtract YearMonth from DateTime: Subtracts a positive or negative YearMonth duration from the specified DateTime value (xs:dateTime). Returns a DateTime that’s respectively before or after the specified DateTime.
• Time zone from Date: Returns the time zone from the specified Date value (xs:date).
• Time zone from DateTime: Returns the time zone from the specified DateTime value (xs:dateTime).
• Time zone from Time: Returns the time zone from the specified Time value (xs:time).
• Year from Date: Returns the year from the specified Date value (xs:date).
• Year from DateTime: Returns the year from the specified DateTime value (xs:dateTime).

## DateTime

This function states that it will create and returns a DateTime value based on the specified Date and Time.

Behind the scenes, this function is translated to the following XPath function: dateTime(xs:date(\$arg1), xs:time(\$arg1))

• dateTime(xs:date(\$arg1), xs:time(\$arg1)) as xs:dateTime

Rules:

• The `\$arg`1 needs to be an xs:date in the following format yyyy-MM-DD or yyyy-MM-DDZ.
• The `\$arg`2 needs to be an xs:time in the following format HH:mm:ss or HH:mm:ss-hh:mm.

Sample:

• The following Data Mapper transformation rule: datetime(“2023-07-27”, “12:00:00”) will be translated to {dateTime(xs:date(‘2023-07-27?), xs:time(’12:00:00’))} and the return will be 2023-07-27T12:00:00

## Day from Date

This function states that it will return the day from the specified Date value (xs:date).

Behind the scenes, this function is translated to the following XPath function: day-from-date(xs:date(\$arg))

• `fn:day-from-date`(`\$arg as xs:date?`)` as xs:integer?`

Rules:

• The function returns an `xs:integer` between 1 and 31, both inclusive, representing the day component in the localized value of `\$arg`.
• The `\$arg` needs to be an xs:date in the following format yyyy-MM-DD or yyyy-MM-DDZ.

Sample:

• The following Data Mapper transformation rule: day-from-date(“2023-07-27”) will be translated to {day-from-date(xs:date(‘2023-07-27’))} and the return will be 27.

## Day from DateTime

This function states that it will return the day from the specified DateTime value (xs:dateTime).

Behind the scenes, this function is translated to the following XPath function: day-from-dateTime(xs:dateTime(\$arg))

• `fn:day-from-dateTime`(`\$arg as xs:dateTime?`)` as xs:integer?`

Rules:

• The function returns an `xs:integer` between 1 and 31, both inclusive, representing the day component in the localized value of `\$arg`.
• The `\$arg` needs to be an xs:dateTime in the following format yyyy-MM-DDTHH:mm:ss or yyyy-MM-DDTHH:mm:ss-hh:mm.

Sample:

• The following Data Mapper transformation rule: day-from-datetime(“2023-07-27T12:00:00”) will be translated to {day-from-dateTime(xs:dateTime(‘2023-07-27T12:00:00’))} and the return will be 27.

## Equal Date

This function states that it will return true or false based on whether the specified Date values are equal.

Behind the scenes, this function is translated to the following XPath expression: xs:date(\$arg1) = xs:date(\$arg2)

• xs:date(\$arg1) = xs:date(\$arg2) as xs:boolean

Rules:

• The `\$arg`1 and \$arg2 need to be an xs:date in the following format yyyy-MM-DD or yyyy-MM-DD-hh:mm

Sample:

• The following Data Mapper transformation rule: is-equal-date(current-date(), current-date()) will be translated to {xs:date(current-date()) = xs:date(current-date())} and the return will be true.

## Equal DateTime

This function states that it will return true or false based on whether with the specified DateTime values are equal.

Behind the scenes, this function is translated to the following XPath expression: xs:dateTime(\$arg1) = xs:dateTime(\$arg2)

• xs:dateTime(\$arg1) = xs:dateTime(\$arg2) as xs:boolean

Rules:

• The `\$arg`1 and \$arg2 need to be an xs:dateTime in the following format yyyy-MM-DDTHH:mm:ss or yyyy-MM-DDTHH:mm:ss-hh:mm.

Sample:

• The following Data Mapper transformation rule: is-equal-datetime(current-dateTime(), current-dateTime()) will be translated to {xs:dateTime(current-dateTime()) = xs:dateTime(current-dateTime())} and the return will be true.

## Equal Day

This function states that it will return true or false based on whether the specified Day values (xs:gDay) are equal with the same starting time when the day values are in the same month and year.

Behind the scenes, this function is translated to the following XPath expression: xs:gDay(\$arg1) = xs:gDay(\$arg2)

• xs:gDay(\$arg1) = xs:gDay(\$arg2) as xs:boolean

Rules:

• Returns true if the two `xs:gDay` values (xs:gDay(“—25-14:00”)) have the same starting instant, when considered as days in the same month of the same year.

Sample:

• The following Data Mapper transformation rule: is-day-equal(day-from-date(…), day-from-date(…)) will be translated to xs:gDay(day-from-date(xs:date(‘2023-07-27’))) = xs:gDay(day-from-date(xs:date(‘2023-07-27’)))} and the return will be true.

## Equal Month

This function states that it will return true or false based on whether the specified Month values (xs:gMonth) have the same starting time when the month values are in the same year.

Behind the scenes, this function is translated to the following XPath expression: xs:gMonth(\$arg1) = xs:gMonth(\$arg2)

• xs:gMonth(\$arg1) = xs:gMonth(\$arg2) as xs:boolean

Rules:

• Returns true if the two `xs:gMonth` values (xs:gMonth(“–12-14:00”)) have the same starting instant, when considered as months in the same year.

Sample:

• The following Data Mapper transformation rule: is-month-equal(current-date(), current-date()) will be translated to {xs:gMonth(current-date()) = xs:gMonth(current-date())} and the return will be true.

## Equal MonthDay

This function states that it will return true or false based on whether the specified MonthDay values (xs:gMonthDay) are equal with the same starting time when the day values are in the same year.

Behind the scenes, this function is translated to the following XPath expression: xs:gMonthDay(\$arg1) = xs:gMonthDay(\$arg2)

• xs:gMonthDay(\$arg1) = xs:gMonthDay(\$arg2) as xs:boolean

Rules:

• Returns true if the two `xs:gMonthDay` values (xs:gMonthDay(“–12-25-14:00”)) have the same starting instant, when considered as days in the same year.

Sample:

• The following Data Mapper transformation rule: is-monthday-equal(current-date(), current-date()) will be translated to {xs:gMonthDay(current-date()) = xs:gMonthDay(current-date())} and the return will be true.

## Equal Time

This function states that it will return true or false based on whether the specified Time values are equal.

Behind the scenes, this function is translated to the following XPath expression: xs:time(\$arg1) = xs:time(\$arg2)

• xs:time(\$arg1) = xs:time(\$arg2) as xs:boolean

Rules:

• Returns `true` if the two `xs:time` values represent the same instant in time, when treated as being times on the same date, before adjusting the timezone.

Sample:

• The following Data Mapper transformation rule: is-equal-time(current-time(), current-time()) will be translated to xs:time(current-time()) = xs:time(current-time()) and the return will be true.

## Equal Year

This function states that it will return true or false based on whether the specified Year values (xs:gYear) have the same starting time.

Behind the scenes, this function is translated to the following XPath expression: xs:gYear(\$arg1) = xs:gYear(\$arg2)

• xs:gYear(\$arg1) = xs:gYear(\$arg2) as xs:boolean

Rules:

• Returns true if the two `xs:gYear` values (xs:gYear(“2005+12:00”)) have the same starting instant.

Sample:

• The following Data Mapper transformation rule: is-year-equal(current-date(), current-date()) will be translated to {xs:gYear(current-date()) = xs:gYear(current-date())} and the return will be true.

## Equal YearMonth

This function states that it will return true or false based on whether the specified YearMonth values (xs:gYearMonth) are the same.

Behind the scenes, this function is translated to the following XPath expression: xs:gYearMonth(\$arg1) = xs:gYearMonth(\$arg2)

• xs:gYearMonth(\$arg1) = xs:gYearMonth(\$arg2) as xs:boolean

Rules:

• Returns true if the two `xs:gYearMonth` values (xs:gYearMonth(“1986-02”)) have the same starting instant.

Sample:

• The following Data Mapper transformation rule: is-yearmonth-equal(current-date(), current-date()) will be translated to {xs:gYearMonth(current-date()) = xs:gYearMonth(current-date())} and the return will be true.

Stay tuned for the third part of this blog post.

Hope you find this helpful! So, if you liked the content or found it useful and want to help me write more, you can buy (or help buy) my son a Star Wars Lego!

##### Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc.

He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.

## Overview

Date and Time functions are used to perform a variety of operations over Dates, such as retrieving the current date and time or adding dates, etc. If you come from the BizTalk Server background or are migrating BizTalk Server projects, they are the equivalent of Date/Time Functoids inside BizTalk Mapper Editor.

## Available Functions

The Date and Time functoids are:

• Add days: Adds a positive or negative number of days to the specified timestamp. Returns a timestamp that’s respectively later or earlier than the specified timestamp.
• Add DayTime to Date: Adds a positive or negative DayTime duration to the specified Date value (xs:date). Returns a Date that’s respectively after or before the specified Date.
• Add DayTime to DateTime: Adds a positive or negative DayTime duration to the specified DateTime value (xs:dateTime). Returns a DateTime that’s respectively after or before the specified DateTime.
• Add DayTime to Time: Adds a positive or negative DayTime duration to the specified Time value (xs:time). Returns a Time that’s respectively after or before the specified Time. Durations that wrap around past midnight also return an earlier Time.
• Add YearMonth to DateTime: Adds a positive or negative YearMonth duration to the specified DateTime value (xs:dateTime). Returns a DateTime that’s respectively after or before the specified DateTime.
• Adjust Date: Adjusts the specified Date value (xs:date) to the current or dynamic time zone.
• Adjust DateTime: Adjusts the specified DateTime value (xs:dateTime) to the current or dynamic time zone.
• Adjust Time: Adjusts the specified Time value (xs:time) to the current or dynamic time zone.
• Current date: Returns the current date in YYYY-MM-DD format.
• Current DateTime value: Returns the current date and time in YYYY-MM-DDThh:mm:ss format.
• Current time: Returns the current date and time in YYYY-MM-DDThh:mm:ss format.
• DateTime: Creates and returns a DateTime value based on the specified Date and Time.
• Day from Date: Returns the day from the specified Date value (xs:date).
• Day from DateTime: Returns the day from the specified DateTime value (xs:dateTime).
• Equal Date: Returns true or false based on whether the specified Date values are equal.
• Equal DateTime: Returns true or false based on whether with the specified DateTime values are equal.
• Equal Day: Returns true or false based on whether the specified Day values (xs:gDay) are equal with the same starting time when the day values are in the same month and year.
• Equal Month: Returns true or false based on whether the specified Month values (xs:gMonth) have the same starting time when the month values are in the same year.
• Equal MonthDay: Returns true or false based on whether the specified MonthDay values (xs:gMonthDay) are equal with the same starting time when the day values are in the same year.
• Equal Time: Returns true or false based on whether the specified Time values are equal.
• Equal Year: Returns true or false based on whether the specified Year values (xs:gYear) have the same starting time.
• Equal YearMonth: Returns true or false based on whether the specified YearMonth values (xs:gYearMonth) are the same.
• Greater Date: Returns true or false based on whether the first Date value is later than the second Date value.
• Greater DateTime: Returns true or false based on whether the first DateTime value is later than the second DateTime value.
• Greater Time: Returns true or false based on whether the first Time value is later than the second Time value.
• Hours from DateTime: Returns the hours from the specified DateTime value (xs:dateTime).
• Hours from Time: Returns the hours from the specified Time value (xs:time).
• Less Date: Returns true or false based on whether the first Date value is earlier than the second Date value.
• Less DateTime: Returns true or false based on whether the first DateTime value is earlier than the second DateTime value.
• Less Time: Returns true or false based on whether the first Time value is earlier than the second Time value.
• Minutes from DateTime: Returns the minutes from the specified DateTime value (xs:dateTime).
• Minutes from Time: Returns the minutes from the specified Time value (xs:time).
• Month from Date: Returns the month from the specified Date value (xs:date).
• Month from DateTime: Returns the month from the specified DateTime value (xs:dateTime).
• Seconds from DateTime: Returns the seconds from the specified DateTime value (xs:dateTime).
• Seconds from Time: Returns the seconds from the specified Time value (xs:time).
• Subtract Dates: Returns the DayTimeDuration value (xs:dayTimeDuration) representing the elapsed time between the starting times for the specified Date values.
• Subtract DateTimes: Returns a DayTimeDuration value (xs:dayTimeDuration) representing the elapsed time between the specified DateTime values..
• Subtract DateTime from Date: Subtracts a positive or negative DayTime duration from the specified Date value (xs:date). Returns a Date that’s respectively before or after the specified Date..
• Subtract DateTime from DateTime: Subtracts a positive or negative DayTime duration from the specified DateTime value (xs:dateTime). Returns a DateTime that’s respectively before or after the specified DateTime.
• Subtract DateTime from Time: Subtracts a positive or negative Time duration from the specified Time value (xs:time). Returns a Time that’s respectively before or after the specified Time. A duration that wraps around past midnight also returns a later Time.
• Subtract Times: Returns a DayTimeDuration value (xs:dayTimeDuration) representing the elapsed time between the specified Time values, which are treated as times on the same date.
• Subtract YearMonth from Date: Subtracts a positive or negative YearMonth duration from the specified Date value (xs:date). Returns a Date that’s respectively before or after the specified Date.
• Subtract YearMonth from DateTime: Subtracts a positive or negative YearMonth duration from the specified DateTime value (xs:dateTime). Returns a DateTime that’s respectively before or after the specified DateTime.
• Time zone from Date: Returns the time zone from the specified Date value (xs:date).
• Time zone from DateTime: Returns the time zone from the specified DateTime value (xs:dateTime).
• Time zone from Time: Returns the time zone from the specified Time value (xs:time).
• Year from Date: Returns the year from the specified Date value (xs:date).
• Year from DateTime: Returns the year from the specified DateTime value (xs:dateTime).

This function states that it will add a positive or negative number of days to the specified timestamp. Returns a timestamp that’s respectively later or earlier than the specified timestamp.

To be documented in the future.

This function states that it will add a positive or negative DayTime duration to the specified Date value (xs:date). Returns a Date that’s respectively after or before the specified Date.

Behind the scenes, this function is translated to the following XPath function: xs:date(xs:date(\$arg1) + xs:dayTimeDuration(\$arg2))

• The expression xs:date(xs:date(\$arg1) + xs:dayTimeDuration(\$arg2)) returns `xs:date`

Rules:

• The `\$arg`1 needs to be an xs:date in the following format yyyy-MM-DD or yyyy-MM-DDZ.
• The `\$arg`2 needs to be an interval in ISO 8601 format. ISO-8601 standard was created to elaborate crystal clear language to discuss dates and periods and avoid misinterpretation. Where Duration (“how long“) is represented with the format P[n]Y[n]M[n]DT[n]H[n]M[n]S, where n is a number.
• The letters mean the representation of date and time:
• P express that this is a period format and should be the first character;
• Y for years; M for months; D for days;
• T is the delimiter for the time;
• H for hours; M for minutes; S for seconds;
• Therefore PT means Period of Time.
• PT20S: parses as 20 seconds.
• PT15M: parses as 15 minutes (where a minute is 60 seconds).
• PT10H: parses as 10 hours (where an hour is 3600 seconds).
• P2D: parses as 2 days (where a day is 24 hours or 86400 seconds).
• P2DT3H4M: parses as 2 days, 3 hours, and 4 minutes.

Sample:

• The following Data Mapper transformation rule: add-daytime-to-date(“2023-07-25”, “P2D”) will be translated to {xs:date(xs:date(‘2023-07-25’) + xs:dayTimeDuration(‘P2D’))} and the return will be 2023-07-27

This function states that it will add a positive or negative DayTime duration to the specified DateTime value (xs:dateTime). Returns a DateTime that’s respectively after or before the specified DateTime.

Behind the scenes, this function is translated to the following XPath function: xs:dateTime(xs:dateTime(\$arg1) + xs:dayTimeDuration(\$arg1))

• `The expression xs:dateTime(xs:dateTime(\$arg1) + xs:dayTimeDuration(\$arg1)) returns xs:date`Time

Rules:

• The `\$arg`1 needs to be an xs:dateTime in the following format yyyy-MM-DDTHH:mm:ss or yyyy-MM-DDTHH:mm:ss-hh:mm.
• The `\$arg`2 needs to be an interval in ISO 8601 format. ISO-8601 standard was created to elaborate crystal clear language to talk about dates and periods and avoid misinterpretation. Where Duration (“how long“) is represented with the format P[n]Y[n]M[n]DT[n]H[n]M[n]S, where n is a number.
• The letters mean the representation of date and time:
• P express that this is a period format and should be the first character;
• Y for years; M for months; D for days;
• T is the delimiter for the time;
• H for hours; M for minutes; S for seconds;
• Therefore PT means Period of Time.
• PT20S: parses as 20 seconds.
• PT15M: parses as 15 minutes (where a minute is 60 seconds).
• PT10H: parses as 10 hours (where an hour is 3600 seconds).
• P2D: parses as 2 days (where a day is 24 hours or 86400 seconds).
• P2DT3H4M: parses as 2 days, 3 hours, and 4 minutes.

Sample:

• The following Data Mapper transformation rule: add-daytime-to-datetime(“2023-07-25T12:00:00”, “PT20S”) will be translated to {xs:dateTime(xs:dateTime(‘2023-07-25T12:00:00’) + xs:dayTimeDuration(‘PT20S’))} and the return will be 2023-07-25T12:00:20

This function states that it will add a positive or negative DayTime duration to the specified Time value (xs:time). Returns a Time that’s respectively after or before the specified Time. Durations that wrap around past midnight also return at an earlier Time.

Behind the scenes, this function is translated to the following XPath function: xs:time(xs:time(\$arg1) + xs:dayTimeDuration(\$arg2))

• `The expression` xs:time(xs:time(\$arg1) + xs:dayTimeDuration(\$arg2)) returns xs:time

Rules:

• The `\$arg`1 needs to be an xs:time in the following format HH:mm:ss or HH:mm:ss-hh:mm.
• The `\$arg`2 needs to be an interval in ISO 8601 format. ISO-8601 standard was created to elaborate crystal clear language to discuss dates and periods and avoid misinterpretation. Where Duration (“how
• The letters mean the representation of date and time:
• P express that this is a period format and should be the first character;
• Y for years; M for months; D for days;
• T is the delimiter for the time;
• H for hours; M for minutes; S for seconds;
• Therefore PT means Period of Time.
• PT20S: parses as 20 seconds.
• PT15M: parses as 15 minutes (where a minute is 60 seconds).
• PT10H: parses as 10 hours (where an hour is 3600 seconds).
• P2D: parses as 2 days (where a day is 24 hours or 86400 seconds).
• P2DT3H4M: parses as 2 days, 3 hours, and 4 minutes.

Sample:

• The following Data Mapper transformation rule: add-daytime-to-time(“11:12:00”, “PT1M”) will be translated to {xs:time(xs:time(’11:12:00?) + xs:dayTimeDuration(‘PT1M’))} and the return will be 11:13:00

This function states that it will Add a positive or negative YearMonth duration to the specified DateTime value (xs:dateTime). Returns a DateTime that’s respectively after or before the specified DateTime.

Behind the scenes, this function is translated to the following XPath function: xs:date(xs:date(\$arg1) + xs:yearMonthDuration(\$arg2))

• The expression xs:date(xs:date(\$arg1) + xs:yearMonthDuration(\$arg2)) returns `xs:date`

Rules:

• The `\$arg`1 needs to be an xs:date in the following format yyyy-MM-DD or yyyy-MM-DDZ.
• The `\$arg`2 needs to be an interval in ISO 8601 format. ISO-8601 standard was created to elaborate crystal clear language to discuss dates and periods and avoid misinterpretation. Where Duration (“how long“) is represented with the format P[n]Y[n]M, where n is a number.
• The letters mean the representation of date and time:
• P express that this is a period format and should be the first character;
• Y for years; M for months;
• Therefore PT means Period of Time.
• PT1Y: parses as 1 Year.
• P1M: parses as 1 Month.

Sample:

• The following Data Mapper transformation rule: add-yearmonth-to-date(“2023-07-26Z”, “P1M”) will be translated to {xs:date(xs:date(‘2023-07-26Z’) + xs:yearMonthDuration(‘P1M’))} and the return will be 2023-08-26Z.

This function states that it will adjust the specified Date value (xs:date) to the current or dynamic time zone, or to no timezone at all; the result is the date in the target timezone that contains the starting instant of the supplied date.

Behind the scenes, this function is translated to the following XPath function: `adjust-date-to-timezone`(xs:date(\$arg))

• `fn:adjust-date-to-timezone`(`\$arg as xs:date?`)` as xs:date?`

Rules:

• The `\$arg` needs to be an xs:date in the following format yyyy-MM-DD or yyyy-MM-DDZ.

Sample:

• The expression `fn:adjust-date-to-timezone(xs:date("2002-03-07"))` returns `xs:date("2002-03-07-05:00")`.

This function states that it will adjust the specified DateTime value (xs:dateTime) to the current or dynamic time zone, or to no timezone at all.

Behind the scenes, this function is translated to the following XPath function: adjust-dateTime-to-timezone(xs:dateTime(\$arg))

• `fn:adjust-dateTime-to-timezone`(`\$arg as xs:dateTime?`)` as xs:dateTime?`

Rules:

• The `\$arg` needs to be an xs:dateTime in the following format yyyy-MM-DDTHH:mm:ss or yyyy-MM-DDTHH:mm:ss-hh:mm.

Sample:

• The expression fn:adjust-dateTime-to-timezone(xs:dateTime(‘2002-03-07T10:00:00-07:00’)) returns xs:dateTime(‘2002-03-07T12:00:00-05:00’).

This function states that it will adjust the specified Time value (xs:time) to the current or dynamic time zone, or to no timezone at all.

Behind the scenes, this function is translated to the following XPath function: adjust-time-to-timezone(xs:time(\$arg))

• `fn:adjust-time-to-timezone(\$arg as xs:time?) as xs:time?`

Rules:

• The `\$arg` needs to be an xs:time in the following format HH:mm:ss or HH:mm:ss-hh:mm.

Sample:

• The expression fn:adjust-time-to-timezone(xs:time(“10:00:00”)) returns xs:time(“10:00:00-05:00”).

## Current date

This function states that it will return the current date in YYYY-MM-DD format. It depends on the implicit timezone (in many cases, if not all, it will include the offset).

Behind the scenes, this function is translated to the following XPath function: current-date()

• `fn:current-date`()` as xs:date`

Rules:

• Returns `xs:date(fn:current-dateTime())`. This is an `xs:date` (with timezone) that is current at some time during the evaluation of a query or transformation in which `fn:current-date` is executed.
• The returned date will always have an associated timezone, which will always be the same as the implicit timezone in the dynamic context.

Sample:

• For example, a call of `fn:current-date()` might return `2023-07-21+01:00`.

## Current DateTime value

This function states that it will return the current date and time in YYYY-MM-DDThh:mm:ss format.

Behind the scenes, this function is translated to the following XPath function: current-dateTime()

• `fn:current-dateTime`()` as xs:dateTimeStamp`

Rules:

• Returns the current dateTime (with timezone) from the dynamic context. This is an `xs:dateTime` that is current at some time during the evaluation of a query or transformation in which `fn:current-dateTime` is executed.

Sample:

• For example, a call of `fn:current-dateTime()` might return 2023-07-26T23:43:42.392+01:00.

## Current time

This function states that it will return the current date and time in YYYY-MM-DDThh:mm:ss format. It depends on the implicit timezone (in many cases, if not all, it will include the offset – 23:19:30.985+01:00).

Behind the scenes, this function is translated to the following XPath function: current-time()

• `fn:current-time`()` as xs:time`

Rules:

• Returns `xs:time(fn:current-dateTime())`. This is an `xs:time` (with timezone) that is current at some time during the evaluation of a query or transformation in which `fn:current-time` is executed.
• The returned time will always have an associated timezone, which will always be the same as the implicit timezone in the dynamic context.

Sample:

• For example, a call of `fn:current-time()` might return `23:19:30.985+01:00`.

Stay tune for the second part of this blog post.

Hope you find this helpful! So, if you liked the content or found it useful and want to help me write more, you can buy (or help buy) my son a Star Wars Lego!

##### Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc.

He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.

## Logic App Consumption: Send Query Reports from Log Analytics Workspace

In this blog post and tutorial, what we pretend to do is to create a Logic App, in this case, a Logic App Consumption, that sends reports through Log Analytics using an Azure Function to retrieve the data dynamically.

To do this, first, we need to understand a few things. First, what is the Log Analytics Workspace?

## Log Analytics Workspace

A Log Analytics workspace is a unique environment for logging data from Azure Monitor and other Azure services, such as Microsoft Sentinel and Microsoft Defender for Cloud. Each workspace has its own data repository and configuration but might combine data from multiple services. It is also a centralized place where you can store, collect, and analyze data from various sources.

Think of it as a tool that helps you keep track of everything happening across your Azure environment. It allows you to perform advanced analytics and visualize data in real-time to help you identify and troubleshoot issues quickly. You can also use it to create custom queries and alerts, set up automation tasks, and integrate with other Azure services to get even more insights into your data. Overall, the log analytics workspace is a powerful tool that helps you stay on top of your Azure environment and ensure everything runs smoothly.

As explained above, we can create custom queries to retrieve data from Azure resources, such as Logic Apps, and how does that work?

First of all, you need to create a Log Analytics Workspace. For that:

• Search for Log Analytics in the search bar in Azure Portal and click on Log Analytics workspaces.
• And next, click on Create.
• After this, populate the fields with the more appropriate information for your scenario, like the Name, Resource group, and Region, then click Review and Create.

You should have this once the resource creation is finished, and this is your Log Analytics Workspace.

And if you click on Logs, it will open a panel that works via queries.

Some of them are already built-in queries, but if you click on the X.

You will have a panel where you can write your own queries. And they work based on a scope. For example, you can apply this query to your resource group as you can apply your query to your Logic App. You also have a time range to apply the query to events that happened in the timespan you have defined.

But about queries on Azure Log Analytics, how do they work? And how to write them?

In Log Analytics on the Azure portal, queries are used to search and analyze the data that has been collected in the Log Analytics workspace. Think of queries as a way to ask questions about your data, such as, How many times did this event occur? or What was the average response time for this API? Once you write a query, the Log Analytics workspace will return the results in a table format, which you can use to gain insights and make data-driven decisions.

To write a query in Log Analytics on the Azure portal, you will use a query language called Kusto Query Language (KQL). And what is Kusto Query Language (KQL)?

Kusto Query Language (KQL) is a query language used in Azure Data Explorer, Azure Monitor, and Log Analytics for querying large datasets. KQL is a simple yet powerful language that allows you to search, analyze, and visualize data in a flexible way. KQL has a SQL-like syntax but also supports many features that are specific to Azure services, such as functions for querying JSON data, time-series data, and hierarchical data.

Some of the key features of KQL include:

• Support for querying structured, semi-structured, and unstructured data
• Built-in functions for working with dates, strings, arrays, and other data types
• Support for aggregations, joins, and subqueries
• Ability to query data in real-time
• Integration with Azure services for data collection, storage, and visualization
• KQL is a powerful tool that can help you gain insights and make data-driven decisions, especially when dealing with large datasets in Azure.

Here is an example of KQL:

```AzureDiagnostics
| where Category == "LogicAppWorkflowRuntime" and WorkflowName == "my-logic-app"
| where OperationName == "WorkflowRunStarted" or OperationName == "WorkflowRunCompleted"
| project WorkflowName, OperationName, StartTime, EndTime, Status, Message```

This query will:

• Search for all events in the AzureDiagnostics table where the Category is LogicAppWorkflowRuntime, and the WorkflowName is my-logic-app.
• It will then filter the results only to include events where the OperationName is either WorkflowRunStarted or WorkflowRunCompleted.
• The project operator is used to select specific columns to include in the results. In this case, the query will return each event’s WorkflowName, OperationName, StartTime, EndTime, Status, and Message.
• | is the pipe operator, which connects the different parts of the query together.

This query can help you monitor the performance and status of your Logic App by tracking when workflows are started and completed, as well as any associated error messages or status codes. You can run this query in Azure Log Analytics or Azure Monitor to gain insights into your logic app’s performance and troubleshoot any issues that arise.

## How to configure a Logic App to send data to the Log Analytics workspace

So, now you have an idea of how it works, but, as we explained before, Azure Log Analytics collects the events from various Azure Resources, so to make this possible, we need to create a connection between the Resource we want to collect data from, and the Azure Log Analytics.

To do that, let’s create a Logic App, and in doing so, do not forget to use the same Resource Group and Region you have your Azure Log Analytics stored, and give the Logic App a name that makes sense to you and implement the desired business logic.

Do not forget: Start using Proper names from day one!

• Next, click Diagnostic Settings and + Add diagnostic setting on your Logic App.
• Give a name to your Diagnostic setting name, and check the boxes:
• allLogs
• AllMetrics
• and Send to Log Analytics Workspace
• Next, choose the subscription where your Log Analytics Workspace is created and choose your Log Analytics Workspace, the one we just created.

And from now on, this Logic App will send data to the Log Analytics!

## Create a Logic App to create a report from Log Analytics Workspace

To do that, we need to:

• Create a new Logic App and add a Recurrence trigger and set the following configurations:
• Choose the Interval as 1 and the Frequency Day
• Next, choose the operation Azure Monitor Logs and the ActionRun query and list results.
• Next, you will have some fields to populate, like:
• Subscription
• Resource Group
• Resource Type
• Resource Name
• Query
• Time Range
• This is the query we will be using:
```AzureDiagnostics
| where ResourceProvider == "MICROSOFT.LOGIC"
| where Category == "WorkflowRuntime"
| where status_s == "Failed"
| project LogicAppName = tostring(resource_workflowName_s), message = tostring(error_message_s)
| summarize count() by LogicAppName, message```

In our case, we are dealing with a Logic App that has given us some errors already.

This is the raw output from the query if we run our logic app:

```{
"statusCode": 200,
"Pragma": "no-cache",
"Transfer-Encoding": "chunked",
"Vary": "Accept-Encoding",
"Cache-Control": "no-store, no-cache",
"x-ms-request-id": "e8945bb2-f438-4ee9-9b22-58ae9971e462",
"Strict-Transport-Security": "max-age=31536000; includeSubDomains",
"X-Content-Type-Options": "nosniff",
"X-Frame-Options": "DENY",
"Timing-Allow-Origin": "*",
"x-ms-apihub-cached-response": "false",
"x-ms-apihub-obo": "false",
"Date": "Mon, 13 Mar 2023 12:47:12 GMT",
"Content-Type": "application/json; charset=utf-8",
"Expires": "-1",
"Content-Length": "2536"
},
"body": {
"value": [
{
"LogicAppName": "LA-AppInsights-POC",
"message": "",
"count_": 8
},
{
"LogicAppName": "LA-AppInsights-POC",
"message": "An action failed. No dependent actions succeeded.",
"count_": 17
},
{
"LogicAppName": "LA-AppInsights-POC",
"message": "The execution of template action 'For_each' failed: the result of the evaluation of 'foreach' expression '@{body('Get_Logic_App_Consumption_Failures_By_Type_From_Log_Analytics')?['value']} ------@{items('For_each_2')}---@{items('For_each_2')?['count_']}---@{items('For_each_2')?['message']}' is of type 'String'. The result must be a valid array.",
"count_": 7
},
{
"LogicAppName": "LA-AppInsights-POC",
"message": "Unable to process template language expressions in action 'DecodedContent' inputs at line '0' and column '0': 'The template language function 'decodeBase64' was invoked with a parameter that is not valid. The value cannot be decoded from base64 representation.'.",
"count_": 2
},
{
"LogicAppName": "LA-AppInsights-POC",
"message": "The 'from' property value in the 'table' action inputs is of type 'Object'. The value must be of type 'Array'.",
"count_": 1
},
{
"LogicAppName": "LA-AppInsights-POC",
"message": "The provided 'Http' action inputs are not valid. A request body must not be included for 'GET' requests.",
"count_": 1
},
{
"LogicAppName": "LA-AppInsights-POC",
"message": "Unable to process template language expressions in action 'Initialize_variable_2' inputs at line '0' and column '0': 'The template language function 'decodeBase64' expects one parameter: the string to decode from base64 representation. The function was invoked with '0' parameters. Please see https://aka.ms/logicexpressions#decodeBase64 for usage details.'.",
"count_": 1
},
{
"LogicAppName": "LA-AppInsights-POC",
"message": "Unable to process template language expressions in action 'Initialize_variable_2' inputs at line '0' and column '0': 'The template language function 'decodeBase64' was invoked with a parameter that is not valid. The value cannot be decoded from base64 representation.'.",
"count_": 1
},
{
"LogicAppName": "LA-AppInsights-POC",
"message": "The 'from' property value in the 'table' action inputs is of type 'String'. The value must be of type 'Array'.",
"count_": 1
},
{
"LogicAppName": "LA-Monitoring-Telemetry",
"message": "",
"count_": 2
},
{
"LogicAppName": "LA-Monitoring-Telemetry",
"message": "An action failed. No dependent actions succeeded.",
"count_": 3
},
{
"LogicAppName": "LA-Monitoring-Telemetry",
"message": "The 'from' property value in the 'table' action inputs is of type 'Null'. The value must be of type 'Array'.",
"count_": 1
}
]
}
}
```

If we use this same query in our Log Analytics Workspace, we get these results:

As you can see, we have a report of the Last 7 days with data including the Logic App Name, the error message, and the count.

This is useful in situations where you or your company or the project you are working on is dealing with a lot of data, and you need to know what Failed, where it Failed, and how many times it Failed. So, since we can query this information in the Log Analytics Workspace, we can do the same in our Logic App, as we explained before, using the Azure Monitor Logs and the ActionRun query and list results.

Now going back to our Logic app, we have two options, send this report as an HTML table as it is using a Data Operation – Create HTML Table, and then send an email with this information.

And what you will receive is something like this (in this example, we are not counting with the Logic App Name)

But this data delivery seems a bit old-fashioned, so why not create something more appealing? Something to be presented in the Body of an email like this:

And to achieve this, what we have done was to use an already HTML base document and an Azure Function to map the Rows into an HTML table. (we are not going to address this topic in this tutorial)

Finally, in our Logic App, we may want to validate if the query got results or if it is empty and then send an email with the report.

• On the body of the email, add the Variable containing the HTML template (and the Azure Function call result), and add a subject and an email.
• Save the Logic App once it is finished.

And now you will get the report from Log Analytics Workspace!

Remember that, for example, if you set the recurrence as daily and the query also is looking for events that have occurred in the last 24 hours, you might not have an email to present since the condition prevents it from sending the email if the Body is null, but this also means there are no flaws in your logic apps, which is always nice!

Thanks to my team member Luís Rigueira for helping me realize and implement this idea.

Hope you find this useful! So, if you liked the content or found it useful and want to help me write more content, you can buy (or help buy) my son a Star Wars Lego!

##### Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc.

He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.

## Logic Apps: How to process CSV files

One of the most ancient and common standards for message representation is using text files (Flat Files) like CSV (Comma Separated Values) or TXT files, many of which are custom-made for their systems. And do not be fooled and think these messages are outdated and rarely used. Still today, many existing integrations and new integrations are made based on Flat Files.

No matter if you are working with Logic Apps Consumption or Logic Apps Standard, there are only two ways to generate a Flat File Schema:

• Using a BizTalk Server Visual Studio Project (Visual Studio 2019 or older, depending on the BizTalk Server version you are using), this is a good option if you have a BizTalk Server environment and if you are migrating BizTalk Server solutions.
• Or using the Azure Logic Apps Enterprise Integration Tools. This is an add-in for the Integration Account project to support XML Schema creation, XML transform designer, and Flat File Schema generation for Visual Studio 2019.

In this blog post, we will address how to create a flat file schema using the Azure Logic Apps Enterprise Integration Tools – in this case, a CSV file – and how to process it using the Logic Apps Consumption. The biggest difference between using Logic App Consumption or Standard is that to process a flat file using Consumption, it is required to have an Integration Account, which is not needed in Standard.

## How to create a Flat File Schema for a CSV file

Normally, CSV files contain structured data so that a comma or semicolon separates individual items in the file, and each record is on a new line. In the image below, the CSV file contains five columns:

• First name
• Last name
• Birth year
• City
• And Zip Code

and on each row, there is a person’s data.

We are going to use this sample as our example.

To create a flat file schema for this CSV file, we need to:

• Open Visual Studio 2019 and create a new project by selecting Create a new project option.
• On the Create a new project window, search for the Integration Account template, select the Integration Account template, and click Next.
• On the Configure your new project window, give a Project name and a Solution name and set the Location. Then click Create.
• Once the Visual Studio project is created, right-click the project name and select the option Add > New Item…
• On the Add New Item window, on the left tree, select Logic Apps and then select the Flat File Schema, give it a name, and click Add.
• This will start the Logic Apps Flat File Schema Wizard. On the Welcome screen, click Next >.
• On the Flat File Schema Information screen, set the following configurations and then click Next >:
• On the Instance file property, click the Browse button to locate the flat file from which the schema will be generated.
• On the Record name property, give a name to define the root node name of your XML message—for example, People.
• On the Target namespace property, set your desired namespace or leave the default.
• On the Code page property, select UTF-8 (65001) from the drop-down selection list.
• Because this is a small message on the Select Document Data screen, leave the default selected data and click Next >.
• We will separate or define what separates a person on this first Select Record Format screen. And in this case, it is the new line. So, select the option By delimiter symbol and click Next >.
• On the Delimiter Record screen, the child delimiter property is already defined to be the new line ({CR}{LF}), so leave the default configuration and click Next >.
• On the Child Elements screen, on the first line, set the following configuration:
• Element Name: Person
• Element Type:  Repeating record
• And on the rest of the lines, set the Element Type to Ignore since all lines represent the Person we will define on the first line. And then click Next >.
• On the Schema View screen, you notice our Schema representation is being created. Click Next >.
• Now, back to the Select Document Data screen, you notice that only the first line is automatically selected, excluding the new line character, and this is what we want. We will be defining the structure of the person object. Click Next >.
• A semicolon separates this document, so in the Select Record Format screen, select the option By delimiter symbol and click Next >.
• On the Delimiter Record screen, change the child delimiter property to a semicolon (;), and click Next >.
• Now you notice that we have all fields separated in the Child Elements screen. Give the following configuration and click Next >.
• FirstName (string)
• LastName (string)
• BirthYear (integer)
• City (string)
• ZipCode (string)
• On the Schema View screen, you notice our Schema representation is finished. Click Finish.
• And the Schema will open inside Visual Studio.

Now that we have our schema created, we need to upload it to our Integration Account – if you don’t have it, you need to create an Integration Account inside the Azure Portal. To do that:

• In the Azure Portal, access your Integration Account and select the Schemas option under Settings.
• On the Schemas page, click in + Add and on the Add Schema panel on the Schema property, browse for the schema we just created and click Ok.
• This will add the schema to your Integration Account, which can be used inside your Logic Apps.

Now we need to create a Logic App to process this CSV. To do that, we need to:

• Create a Logic App and use the HTTP Request-Response template – of course, this can be adapted to your requirements.
• Once the Logic App design is loaded, click Save and return to your Logic App page. From there, click Workflow settings under Settings.
• On the Workflow settings page, under the Integration account section, select the integration account in which we add our previous flat file schema on the Select an Integration account property. And click Save.
• Now back to our Logic App designer. After the When a HTTP request is received trigger, select Add an Action.
• On the Choose an operation panel, search for Flat File and then choose the Flat File > Flat File Decoding action.
• On the Flat File Decoding action, set the following configurations:
• Content: Body from the When a HTTP request is received trigger.
• Schema Name: Persons – the flat file we created earlier.
• Now, select the Response action and set the following configurations:
• Status Code: 200
• Body: Body from the Flat File Decoding action.
• And finally, save the Logic App.

Now if we open Postman and send the following request, the expected response will be the same payload translated to XML, as you see in the picture below:

Hope you find this useful! So, if you liked the content or found it useful and want to help me write more content, you can buy (or help buy) my son a Star Wars Lego!

##### Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc.

He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.

## Flat File Support inside Logic Apps

We live in a REST era where JSON or XML is our text-based format default choice of text-based formats and widespread use by major corporations. However, one of the most ancient and common standards for message representation is using text files (Flat Files) like CSV (Comma Separated Values) or TXT files, many of which are custom-made for their systems. Do not be fooled and think these messages are outdated and rarely used. A good example is EDI messages, which are used extensively by large companies, so it is often necessary to transform text files into XML and vice versa.

Still today, many existing integrations and new integrations are made based on Flat Files.

## What are Flat Files?

The standard definition states that a flat file is a collection of data stored in a two-dimensional database in which similar yet discrete strings of information are stored as records in a table. The table’s columns represent one database dimension, while each row is a separate record. Or in other words, it is a type of data storage file in which data is stored as plain text, often in a table-like structure with rows and columns. Each row represents a single record, while columns represent fields or attributes of the data. The information stored in a flat file is generally alphanumeric with little or no additional formatting. The structure of a flat file is based on a uniform format as defined by the type and character lengths described by the columns.

Flat files serve various purposes in software development, primarily for data storage, exchange, and processing tasks. They are widely used due to their simple structure, human readability, and ease of manipulation across different platforms and applications. For instance, flat files are commonly employed in data import and export operations, where applications or systems with diverse data storage mechanisms must communicate or transfer data.

A flat-file instance message is a text file that can contain three logical parts:

• A body.
• And a trailer.

In that order. Of course, both the header and the trailer are optional. The following example shows a flat-file instance message consisting of all three parts, with the body in bold type:

Sandro Pereira
Porto, Portugal
PO,1,BOOK,4415
TRANS-1

If you come from a Microsoft BizTalk Server background or if you are migrating a BizTalk Server project using these types of Flat-Files, you may know that the Flat file disassembler’s parsing allows you to specify:

• The Header Schema in the Header schema design-time property of the flat file disassembler or the XMLNORM.HeaderSpecName message context property.
• The Body Schema in the Document schema design-time property of the flat file disassembler or the XMLNORM.DocumentSpecName message context property.
• And the Trailer Schema in the Trailer schema design-time property of the flat file disassembler or the XMLNORM.TrailerSpecName message context property.

However, Logic Apps only supports Body Schemas. You cannot have different schemas for Headers, Bodies, and Trailers. You can still process these types of flat-files messages but in a different approach with a single Schema dividing what is a header, body and trailer in different records (structures).

## Flat-File Schema Types

Within a particular part of a flat-file instance message, different data items are grouped into records, which themselves can contain sub-records and, ultimately, individual data items known as fields. These records and fields are distinguished from each other using one of two different basic methodologies.

• The first methodology, known as positional, defines each data item as a pre-established length, with pad characters being used to bring a shorter item of data up to its expected length.
• The second methodology, known as delimited, uses one or more special characters to separate items of data from each other. This methodology avoids the need for otherwise superfluous pad characters. Still, it introduces some special considerations when the data itself contains the character or sequence of characters being used as a delimiter.

### Positional Flat Files

Positional records within a flat-file instance message contain individual fields (items of data) that are each of a predefined length. The fields are parsed according to these lengths. For example, consider the following positional record from a flat-file instance message containing an id, country code, client name, and Country name:

```01 PT Sandro Pereira                       Portugal
```

A reasonable definition for this record in a flat-file schema can be described as follows:

• A positional record named Client contains the following fields:
• An attribute named id that is left-aligned, three characters in length, with a zero character offset.
• An element named countryCode that is left-aligned, three characters in length, with a zero character offset.
• An element named name that is left-aligned, 37 characters in length, with a zero character offset.
• An element named country that is left-aligned, and the length is until the end of the line.

Given these record and field definitions, the Flat file disassembler will produce the following XML equivalent of this record:

```
PT
Sandro Pereira
Portugal

```

There are several considerations related to positional records that will affect how the record is parsed when received and constructed when sent, including:

• The character used to fill the unused portion of each field, known as the pad character.
• An optional tag within the record can be used to distinguish the record from other similar records. Tags usually occur at the beginning of the record but are allowable anywhere within it. Positional records can be defined to have a tag or not have a tag, but once defined, the tag must be present or not, based on the definition.
• How data is justified within a fixed length field relative to the accompanying pad characters.
• Positional records nested within other positional or delimited records.
• Positional records with field lengths specified as a specific number of bytes rather than a specific number of characters.

Notes:

• Suppose your flat file contains both delimited and positional records. In that case, you must set the Structure property of the root node to Delimited and the Structure property of subordinate record nodes to either Delimited or Positional as appropriate.
• Fields in positional records have a limit of 50000000 characters.

### Delimited Flat Files

Delimited records within a flat-file instance message contain nested records and/or individual fields (items of data) that are separated by a predefined character or set of characters. The fields are parsed according to these separating delimiters. For example, consider the following delimited records from a flat-file instance message, which contain three client lines to add to our internal system hypothetically:

```Sandro;Pereira;1978;Crestuma;4415
José;Silva;1972;Crestuma;4415
Rui;Barbosa;1975;Lever;4415
```

A reasonable definition for this record in a flat-file schema can be described as follows:

• A delimited repeating record named Client with child delimiter {CR}{LF}
• And  delimited elements with child delimiter ;
• firstName
• lastName
• birthYear
• city
• zipCode

Given these record and field definitions, the Flat file disassembler produces the following XML equivalent of these records.

```
Sandro
Pereira
1978
Crestuma
4415

...

...
```

There are several considerations related to delimited records that will affect how the record is parsed when received and constructed when sent, including:

• The character or characters are used to override the interpretation of delimiters so that they are treated as part of the data.
• An optional tag at the beginning of the record can be used to distinguish the record from other similar records.
• How data is justified within fields with minimum lengths relative to the accompanying pad characters.
• Positional records nested within other delimited records.
• How data is justified within a fixed length field relative to its accompanying pad characters.

Preservation and suppression of delimiters when flat-file messages are received and sent.

Notes:

• Suppose your flat file contains both delimited and positional records. In that case, you must set the Structure property of the root node to Delimited and the Structure property of subordinate record nodes to either Delimited or Positional as appropriate.
• Delimited fields in flat files have a limit of 50000000 characters.

## How do Logic Apps process the text files (Flat Files)?

When building a logic app workflow in Azure Logic Apps, you can encode and decode flat files using the Flat File built-in connector actions and a flat file schema for encoding and decoding. You can use Flat File actions in multi-tenant Consumption logic app workflows and single-tenant Standard logic app workflows.

• Inside Logic Apps Consumption, Integration Account is required to store the flat-files schemas and use the Flat File built-in connector.
• Inside Logic Apps Standard, there is no need for having an Integration Account since Schemas are supported built-in. However, if you desire, you can still use the Integration Account.

While no Flat File triggers are available, you can use any trigger or action to feed the flat-file content into your workflow. For example, you can use a built-in connector trigger, a managed or Azure-hosted connector trigger available for Azure Logic Apps, or even another app, like the Request built-in trigger or File System trigger.

### Flat File Encoding action

The Flat File Encoding action allows you to convert an XML message into a flat file. Note that this action does not validate the incoming XML message. For that, you need to use the XML Validation action.

In Logic Apps Consumption, this action allows the following inputs:

• The Content property, where you specify the XML message you want to encode to flat-file.
• The Schema Name property is where you choose the flat-file body Schema or the Document schema.
• If the schema list is empty, either your logic app resource isn’t linked to your integration account or doesn’t contain any schema files.
• And then we have the following optional properties:
• Mode of empty node generation, where we specify the mode to use for empty node generation with flat file encoding. Possible values are ForcedDisabled, HonorSchemaNodeProperty, or ForcedEnabled.
• And XML Normalization allows you to enable or disable XML normalization in flat file encoding. Possible values are Yes or No.

In Logic Apps Standard, this action allows the following inputs:

• The Content property, where you specify the XML message you want to encode to flat-file.
• In the Source property, we select either LogicApp or IntegrationAccount as your schema source.
• The Name property is where you choose the flat-file body Schema or the Document schema.
• If the schema list is empty, either your logic app resource isn’t linked to your integration account, your integration account doesn’t contain any schema files, or your logic app resource doesn’t contain any schema files.
• And then we have the following optional properties:
• Mode of empty node generation, where we specify the mode to use for empty node generation with flat file encoding. Possible values are ForcedDisabled, HonorSchemaNodeProperty, or ForcedEnabled.
• And XML Normalization allows you to enable or disable XML normalization in flat file encoding. Possible values are Yes or No.

### Flat File Decoding action

The Flat File Decoding action allows you to convert a flat-file message into an XML message. Note that this action does not validate the outcome XML message. For that, you need to use the XML Validation action.

In Logic Apps Consumption, this action allows the following inputs:

• The Content property, where you specify the flat-file message you want to decode to XML.
• The Schema Name property is where you choose the flat-file body Schema or the Document schema.
• If the schema list is empty, either your logic app resource isn’t linked to your integration account or doesn’t contain any schema files.

In Logic Apps Standard, this action allows the following inputs:

• The Content property, where you specify the flat-file message you want to decode to XML.
• In the Source property, we select either LogicApp or IntegrationAccount as your schema source.
• The Name property is where you choose the flat-file body Schema or the Document schema.
• If the schema list is empty, either your logic app resource isn’t linked to your integration account, your integration account doesn’t contain any schema files, or your logic app resource doesn’t contain any schema files.

Unlike BizTalk Server, where this Syntax Transformations or Data translation typically happens inside Receive or Send Pipelines. Inside Logic Apps, they happen inside our business process, aka Logic App using the Flat File connector.

Hope you find this useful! So, if you liked the content or found it useful and want to help me write more content, you can buy (or help buy) my son a Star Wars Lego!

##### Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc.

He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community.