Comparing dates

[beckyjc]

Hi,

I'm just getting started with Flow and trying to compare a date from Microsoft Dynamics 365 with today's date to send an email if the date from CRM is not greater than or equal to today.

So far, I've got:

@greaterorequals
(
formatDateTime(item()?['abc_latesttimeentered'],'dd-MM-yyyy'),
formatDateTime(utcnow(),'dd-MM-yyyy')
)

This works fine for some records and not others. It seems that it works for those where the (UK format) abc_latesttimeentered date could potentially be misunderstood as US-format, e.g. 03/02/2018 but doesn't work for records with day numbers greater than 12, e.g. 17/03/2018. 

Where am I going wrong? 

Thanks,

Becky

[RodrigoF]

Hey Becky

This might help you. 

https://powerusers.microsoft.com/t5/Building-Flows/Compare-dates/td-p/54802

[beckyjc1]

Hi Rodrigo,

Thanks for this, though I'm not sure using "ticks" will help in this particular situation. 

Would using "ticks" resolve the US vs UK date format issue that I appear to have? I seem to be seeing the correct results for some records and not others.  Am I correct in thinking that using ticks would bring the time component of the date/time into play?  I need to ignore the time in the comparison as am only interested in comparing the date. 

Appreciate any further help or guidance as I'm still stuck on this one.

Thanks,

Becky

[v-xida-msft · Accepted solution]

Hi @beckyjc,

Which Date format does the abc_latesttimeentered column return? Is it a UK format?

Could you please share a screenshot of your flow's configuration?

Further, could you please show a bit more about the result the abc_latesttimeentered column returned?

I think your thought is correct, the first parameter of formatDateTime() function is required to fill a datetime string which must match ISO 8601 format. The UK format datetime string could not match ISO 8601 format. If you convert the UK format datetime string into other format using formatDateTime() function, the error would show up as below:

If the date time the abc_latesttimeentered column returned is a UK format (e.g. 17/03/2018), you could consider take a try to compare with today's date with the following workaround:

• Add a proper trigger, here I use Flow Button trigger.
• Add a Variables-> "Initialize variable" action, Name set to UK_format_datetimes, Type set to String and Value set to 17/03/2018. 
• Add a Compose action, Inputs set to following formula:

split(variables('UK_format_datetimes'),'/')

• Add a "Compose 2" action, Inputs set to following formula:

concat(outputs('Compose')[1],'/',outputs('Compose')[0],'/',outputs('Compose')[2])

Note: This action is used to convert the UK format datetime string (dd/MM/yyyy) into US format datetime string (MM/dd/yyyy).

• Add a Condition, click "Edit in advanced mode", type the following formula:

@not(greaterOrEquals(outputs('Compose_2'), utcNow('MM/dd/yyyy')))

or

@less(outputs('Compose_2'), utcNow('MM/dd/yyyy'))

• Within "If/yes" branch of Condition, add a "Send me an email notification" action.

Image reference:

The flow works successfully as below:

More details about ISO 8601 format, please check the following article:

ISO 8601 format

More details about using expression in flow actions, please check the following article:

Using expression in flow actions

Best regards,

Kris