Showing results for 
Search instead for 
Did you mean: 
Advocate III
Advocate III

Find all records 60 days old

How would i find all records in SQL server whos date is 60 or more days old.


I use:

Filter("MY TABLE", DateDiff(Now(), IN_SYSTEM_DATE) >= 60)


But i keep getting service limitation error. I thought Filter supported delegation with SQL server... How would i acheive this?

Community Champion
Community Champion


I have a solution for you. Since 60 days ago is a static date, you can have it pre-calculated so that your filter does not need it calculated for each row.


Create a DatePicker control to store the static calculation. Set its DefaultDate property to:


This means, "Add negative 60 days to the date and time now." You could use Today() in place of Now().

You can hide the DatePicker control if you don't want it seen. Set its Visible property to false. At this point, you could also get creative by adding a slider to adjust 60 days to whatever other amount you want. In that case, you would adjust DatePicker.DefaultDate to:



Then Filter your table comparing dates against the DatePicker you just created:


Filter("MY TABLE",IN_SYSTEM_DATE<=DatePicker1.SelectedDate)

This means, "Filter my table to show all dates that are older than or equal to 60 days ago, the date calculated in DatePicker.


Note: Delegation is supported with Controls this way; a context variable would not work for this.


Let me know how this works for you.


Mr. Dang

Microsoft Employee

I get an error, "We cannot apply operator < to types DateTimeZone and DateTime.


Any suggestions?

Community Champion
Community Champion

My guess is that there is a mismatch between data types. The dates in your datasource may be formatted differently than the DatePicker, but I really don't know.


This is probably a question for the PA team.



Microsoft Employee

Yes, it is a mismatch of data types. However, I have altered every datetime value i can on the SQL server side, but still no luck.

Community Champion
Community Champion

Hi @Bri

There SQL Server / Azure SQL DB connector currently has many bugs, including the following:

1) After a field contains a value, you cannot set the value back to null from PowerApps
2) There are intermittent issues writing numbers with more than 7 significant figures to the database
3) Date type columns cannot be filtered using > or < operators
4) Writing to date type columns is problematic


As a work-around for 3) and 4) I now store all date information as an integer of value yyyymmdd.  This allows me to filter using > and < operators as well as avoid all pitfalls with time zones.


I know that the PowerApps team is working to resolve all the above, and there should be a new connector out in July sometime.


Hope this helps.


PS I have many posts on all the above and more if you want to search the community

Advocate III
Advocate III

Where do you get this information about what they are working on, and when it will be completed. I know they have a blog, but that does not give any time frames for completion.

That would be a great resource to have.
Community Champion
Community Champion

Hi @Bri

Unfortunately communication from the PowerApps group is generally pretty poor and unstructured.  We have complained about it, but no real progress.  They have now put status on the ideas section but no dates and no details.

I know about the significant figures issue since I wrote to the support team and received a reply to say that they are working on a fix and should be out in July.

@mr-dangthen posted at the bottom of this thread with a link to a video talking about delegation of SQL server connector being updated soon.

Putting two and two together, I just imagine that they are reviewing the whole SQL connector as it is just too buggy at the moment.

Sorry to disappoint.

Not a problem at all, I appreciate all the help. And the SQL connector seems fine for my purposes other than dates/time.

Helpful resources

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Top Solution Authors
Top Kudoed Authors
Users online (1,355)