Delegation Issues - Datetime and DatetimeZone types - SQL Server

[gersontun]

Hello all,

I have the following scenario, there's a gallery that it is expected to have many items (+500), everything is stored in SQL Server.

So for reducing the number of items I need by default filter them by the last week they were created, for that I have two datepickers, start and end, set for today -7 days and today.

Then in the Items, property of the gallery I want to Filter the Datasource but, when doing it like this, I get Datetime and DatetimeZone issue, completely unable to retrieve data.

Then, if I convert my column CreatedTimestamp with a DateTimeValue, I get a delegation issue.

What it is the correct conversion I need to do, so both are DateTime. My column in the database is datetime type. Time will be useful because I still need to do some timezone conversions in order to show the data in the local datetime, not UTC, everything in the DB is saved in UTC format.

Any help is appreciated.

Thanks!

[RandyHayes]

@gersontun 

Filter on a Date Column is not delegable in the SQL connector.

A valid workaround:

1. Direct date filters don't work for SQL Server. However, you can create a calculated column that will work. For instance, ALTER TABLE myTable ADD DateAsInt AS (YEAR([date]) * 10000 + MONTH([date]) * 100 + DAY([date])) and then filter on the calculated number column.

I hope this is helpful for you.

[gersontun · Accepted solution]

Thanks @RandyHayes 

As for now this solution is easier, I just modified the view I am working with, getting the datetime like this:

CREATEDTIMESTAMP at time zone 'UTC' as createdtimestamp_utc

this will add "+00:00" to the column and it seems Power Apps handle this much better, I actually do not need to do any conversion then to local or any specific time zone.  So my filter it is as simple as

I just had to add 1440 minutes to the end date to be inclusive.

I tried to use your solution but results were being kind of inaccurate because of we were not including the time.

I do not know if this modification will then cause some performance issues, but as for now no errors nor warnings shown.

Thanks for your help.

[RandyHayes]

@gersontun 

Actually, what I provided was just a copy/paste from the documentation on the SQL connector.  

I typically do datetime comparisons/criteria differently, so I've only hit the issue you have a couple of times.  It seemed that suggestion in the documents was somewhat feasible.  But if you can use a view...that is even better as long as you only need to read data from it and not needing to write back.