Comparing dates with less/lessOrEquals doen't work

[dimi]

Hello dear community

I am struggeling to get this right, i have a SharePoint document library with a column 'Due Date' (see screenshot for details)

Now i want to check if this Due date is smaller than a future time, lets say 2 months from now.

I have tried several ways using formatDateTime, less, lessOrEqual, setting the values into variables to actually see what i am looking at, format and so on, switching the properties arround (don't know why i tried that ) and so on but i can't seem to get it to work properly.

I am setting a Get Future time with 2 months ahead which has the following format: 2018-09-17T09:14:37.5311657Z

The SharePoint documents have this format: 2018-09-25

Can someone tell me why this isn't working:

	less(
		formatDateTime(items('Apply_to_each')?['Due_x0020_date'],'dd/MM/yyyy'),
		formatDateTime(body('Get_future_time_(2_months_ahead)'),'dd/MM/yyyy')
		)

Or this:

	less(
		items('Apply_to_each')?['Due_x0020_date'],		 
                body('Get_future_time_(2_months_ahead)')
		)

Don't know what i can do more here, i am gonna try to convert the Due date to the same format as the Future time and see if that works. Thank you in advance for any kind of suggestion or help!

Kind regards

[jaylou]

Hi Dimi,

I have a FLOW that runs daily and gets all items where the "target release date" are 2 weeks away. 

It looks like you may just be missing the @

I am using the below in my condition which works well.

@lessOrEquals(items('Apply_to_each')?['ACCTGTARGETRELEASEDATE'], formatDateTime(adddays(utcnow(), 15)

Joe

[dimi]

Hi Jaylou and thank you for responding,

Good point, i didn't look at that. It's strange, i have looked at the original formula:

@equals(less(formatDateTime(items('Apply_to_each')?['Due_x0020_date'], 'dd/MM/yyyy'), formatDateTime(body('Get_future_time_(2_months_ahead)'), 'dd/MM/yyyy')), true)

and

@less(formatDateTime(items('Apply_to_each')?['Due_x0020_date'],'dd/MM/yyyy'),formatDateTime(body('Get_future_time_(2_months_ahead)'),'dd/MM/yyyy'))

and both don't work as i expect. I have several files on SharePoint to perform test against my logic, i have 1 file where the 'Due date' is set to 3 months ahead so that this file shouldn't be 'True' as result of the formula since the 'Due date' IS NOT smaller (less) than the 'Get_future_time_(2_months_ahead)' date time.

I am overseeing something here i think but no clue what.

[dimi · Accepted solution]

Dear

I have solved my problem by formatting the Future datetime to 'yyyy-MM-dd' format

because the date format i receive from SharePoint is in that format, i was to focused on using our format 'dd-MM-yyyy' and had oversee that, i was doing to much than it was needed 

So apparently with using the same format as that from SharePoint the condition statement worked as expected...

To summarize:

Get Future time (2 months ahead)

Set the Future time in a string variable:

formatDateTime(body('Get_future_time_(2_months_ahead)'),'yyyy-MM-dd')

Set the Due date value into a variable:

formatDateTime(items('Apply_to_each')?['Due_x0020_date'], 'yyyy-MM-dd')

Condition:

@equals(less(formatDateTime(items('Apply_to_each')?['Due_x0020_date'], 'yyyy-MM-dd'), formatDateTime(variables('FutureDateVar'), 'yyyy-MM-dd')), true)

PROFIT 

Kind regards.

[hiteshn]

Hi,

There is some problem with the date comparison and I also was getting the wrong comparison results in condition action, after many combinations I found that below combination works correctly for me while comparing dates.

I used formatdatetime function to bring date only and comparing them.

Formats:

greater/less than - 'yyyyMMdd'

equal to - any would work I used - 'dd/MM/yyyy'

e.g. formatDateTime(utcNow(),'dd/MM/yyyy')