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

struggling with dates in dropdown

Hi all , banging my head against the wall .... 

I have a drop down (DropSailDate) that contains dates :
Items: [Blank(),Date(2020,03,28),Date(2020,03,30)]

in my gallery Items : 

If(

DropSailDate.Selected.Value <> Blank() , Filter('[dbo].[PAX_LIST]', SAIL_DATE = DropSailDate.Selected.Value, 

 

the SAIL_DATE column in MSSQL table is date 

 

when dropdown selected , I get no values, when blank , I have the full list populated .

I created 2 variables , X and Y , X contains the distinct values of the database table and  Y is the dropdown selected value. ( please see the attached file ) , they both are the same date format .

what am I doing wrong ?  

1 ACCEPTED SOLUTION

Accepted Solutions
eka24
Super User
Super User

In my early days of powerapps I encountered problems of filtering SQL dates in powerapps until I saw this article that advice and additional date column With :
ALTER TABLE MyTable
ADD MyDateInt AS ((datepart(year,[MyDate])*(10000)+datepart(month,[MyDate])*(100))+datepart(day,[MyDate])) PERSISTED

Read the full article
https://baizini-it.com/blog/index.php/2017/09/28/powerapps-and-azure-sql-database-current-issues/


If like this post, give a thumbs up. Where it solved your issue, Mark as a solution

View solution in original post

4 REPLIES 4
eka24
Super User
Super User

In my early days of powerapps I encountered problems of filtering SQL dates in powerapps until I saw this article that advice and additional date column With :
ALTER TABLE MyTable
ADD MyDateInt AS ((datepart(year,[MyDate])*(10000)+datepart(month,[MyDate])*(100))+datepart(day,[MyDate])) PERSISTED

Read the full article
https://baizini-it.com/blog/index.php/2017/09/28/powerapps-and-azure-sql-database-current-issues/


If like this post, give a thumbs up. Where it solved your issue, Mark as a solution

View solution in original post

cafeolai
Frequent Visitor

Thank you very much for you help , I will try that today   🙂 

 

It just blows my mind that Microsoft cannot recognise a date within 2 Microsoft products !! 

v-siky-msft
Community Support
Community Support

Hi @cafeolai ,

 

This a known limitation for SQL Server connector, direct date filters do not work for SQL Server. (Reference: https://docs.microsoft.com/en-us/connectors/sql/ )

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

  • date
  • datetime
  • datetime2
  • smalldatetime

So, the solution for this issue is to create a calculated column for the date column in SQL table with Integer datatype. The formula for this calculated column is “YEAR([your_date_field]) * 10000 + MONTH([ your_date_field ]) * 100 + DAY([ your_date_field ])“. The data in your table will look like below with new field

CustomerSAIL_DATESAIL_DATEAsInt
Cust 12019-05-0120190501
Cust 22018-11-0820181108
Cust 32015-12-1520151215
Cust 42018-06-0720180607

 

Then in PowerApps just filter on that new Integer column:

 

Filter('[dbo].[PAX_LIST]', SAIL_DATEAsInt =Value(Text(DropSailDate.Selected.Value,”[$-en-US]yyyymmdd”)))

 

Hope this helps.

Sik

 

cafeolai
Frequent Visitor

It works ,

Thank you very much @eka24  and @v-siky-msft  for you help .

still it doesn't make sense to me that Microsoft cannot handle dates , which is the base of any database !!  🤔

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (2,528)