cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gersontun
Frequent Visitor

Delegation Issues - Datetime and DatetimeZone types - SQL Server

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.

gersontun_0-1658410571525.png

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

gersontun_1-1658410619920.png

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!

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

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

gersontun_2-1658436702698.png

 

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.

 

View solution in original post

3 REPLIES 3
RandyHayes
Super User
Super User

@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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

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

gersontun_2-1658436702698.png

 

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
Super User
Super User

@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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (3,206)