Entra Provisioning Expressions FormatDateTime and IIF

 Lately when configuring an Entra ID Enterprise Application for SCIM integration to an external system, I ran into an issue passing through dates. The cause of the problem was that we use an extension attribute to hold this particular date, but we also add text into the field.

My first inclination was to use the IIF expression to do some basic format checking, but this fails as the FormatDateTime function is eager and checks for the value of the field before processing the IIF part. For example this statement (simplified)

IIF(Len([extensionAttribute9])="10", FormatDateTime([extensionAttribute9], "dd/MM/yyyy", "yyyy-MM-dd"), "")

will fail if [extensionAttribute9] has non date values.

The fix was to move the IIF statement inside the FormatDateTime expression, so the example above becomes

FormatDateTime(IIF(Len([extensionAttribute9])="10", [extensionAttribute9], ""), "dd/MM/yyyy", "yyyy-MM-dd")

Moving the logic inside the FormatDateTime expression means that the eager validation evaluates the logical function before applying the value.

IIF Limitations

Something else that came up was limitations in how IIF behaves, notably that it doesn't support AND or OR for multiple comparisons. This is well documented on the expressions page, https://learn.microsoft.com/en-us/entra/identity/app-provisioning/functions-for-customizing-application-data#iif, but did require a rethink.

In the end I found it worked best to check for not equal rather than equal. The final expression ended up as 

FormatDateTime(IIF(IsNullOrEmpty([extensionAttribute9]), "", IIF(Len([extensionAttribute9])<>"10", "", IIF(Instr([extensionAttribute9], "/", , )<"1", IIF(Instr([extensionAttribute9], "-", , )<"1", "", [extensionAttribute9]), [extensionAttribute9]))), "32", "dd/MM/yyyy", "yyyy-MM-dd")

The crude check being check that it's not null or empty, then check for a length of 10 which fits our date format, then check for the presence of "/" or "-" characters and if these all pass then format as date from "dd/MM/yyyy" to "yyyy-MM-dd". Obviously this isn't a super accurate check but given the limitations with the provisioning expressions it should cover most cases and we can update source data for any failures.