Filter a gallery by date for SQL column of type datetime2

(0) Share

Report

Posted on by Digital

1,207

I would like to filter my gallery by an SQL datetime2 column called DueDate

I have got it to work by using the DateAdd function on the DueDate column but this results in a delegation warning.

Filter(
 '[SQLTABLE]',
 DateAdd(DueDate, TimeZoneOffset(DueDate), Minutes) = Dropdown2.Selected.Result
 
)

I originally tried to use the DateAdd and TimeZoneOffset on my drop down of dates which is populated using the same Column.

Unfortunately this does not work and I can't figure out why. Have also tried this using the date picker with no luck.

Distinct(
 '[SQLTABLE]'.DueDate,
 DateAdd(DueDate, TimeZoneOffset(DueDate), Minutes)
)

Categories:

Building Power Apps

All responses (2)

Answers (1)

Sort by

v-albai-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @Digital ,

If you want to filter based on datetime2 in PowerApps, it is not supported.

The following data types cannot be used as query option predicates:

date

datetime

datetime2

smalldatetime

If you want to filter, you need to use this formula to transfer:

YEAR([date]) * 10000 + MONTH([date]) * 100 + DAY([date])

Before using above formula, you need to add a new column. For more detailed information, you can refer to the solution provided by @Michael Colbert in this thread:

https://www.powerappsug.com/communities/community-home/digestviewer/viewthread?GroupId=2243&MessageKey=e179232c-fa83-4bd9-9ef5-813feedd4af8&CommunityKey=9f5c6fd1-bb0c-4ffa-adab-06d3d72c11da&tab=digestviewer

Also you can refer to this video to create a new column to keep the value from your datetime2 column.

https://www.youtube.com/watch?v=t4psUHxvI38

Best regards,

Allen
Verified answer

timl 37,287 Super User 2026 Season 2 on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @Digital

As datetime2 is not supported in Power Apps, my approach would be to create a view and to cast your duedate column to the datetimeoffset datatype (which is supported).

CREATE VIEW vwYourView AS SELECT Column1, Column2, CAST(DueDate AS DATETIMEOFFSET) AS [DueDate] FROM [SQLTable]

You can then filter this view by your DueDate column.

By casting to the datetimeoffset data type, you can more easily filter records against a datepicker control (without needing to covert the selected date in the date picker control to a number in yyyymmdd format), and you can also more easily use the greater than/less than operators in call to filter, whilst also overcoming the bug that I describe here.

http://powerappsguide.com/blog/post/sql-dont-let-this-datetime-bug-catch-you-out