Find all records 60 days old

[Bri]

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?

[mr-dang]

Hi,

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:

DateAdd(Now(),-60)

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:

DateAdd(Now(),-Slider1.Value)

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

[Bri]

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

Any suggestions?

[mr-dang]

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.

@v-micsh-msft

[Bri]

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.

[Meneghino]

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

[Bri]

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.

[Meneghino]

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.

[Bri]

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