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

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

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
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Don't miss the call this month on June 16th - 8a PDT

Top Solution Authors
Top Kudoed Authors
Users online (37,256)