cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Steve_G
Level: Powered On

How do I evaluate a SQL date field in PowerApp

I am hoping someone might be able to assist me in figuring how to evaluate an SQL date field in my PowerApp.

 

I have a connection to a MS SQL database through the on-premise gateway and my connection is retrieving a view containing a selection of text, numeric and date fields.

I want to filter that list for records that match a date selected by a DatePicker control.

 

My date field is referenced as '[dbo].[AB103_CurrentEventBookings]'. EV803_BKG_DATE  

 

But if I try to filter using

=Filter('[dbo].[AB103_CurrentEventBookings]',EV803_BKG_DATE = DatePicker1.SelectedDate)

I get no records. Changing the = to <> gives me all records, so it is an issue with comparing, not that the data isn't there.

I have exhausted all options I can think of, reformating the values to be able to determine equivalence.

 

Can anyone suggest how I correctly do a comparison between a SQL date field and a PowerApps Date field.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: How do I evaluate a SQL date field in PowerApp

Hi @Steve_G 

If '[dbo].[AB103_CurrentEventBookings]' is a view and it's possible for you to modify the definition, you might have more success if you Cast the EV803_BKG_DATE column to the DateTimeOffset data type. The purpose of this is to help overcome a known bug with SQL Server and date filtering.

https://powerusers.microsoft.com/t5/Building-PowerApps-Formerly/Trying-to-filter-or-lookup-on-a-date...

One issue with converting the date to text with Text(EV803_BKG_DATE,"mm/dd/yyyy") is that it results in a non-delegable query. Therefore, your results may not be correct if your source data exceeds 2000 rows.

 

 

View solution in original post

4 REPLIES 4
Dual Super User
Dual Super User

Re: How do I evaluate a SQL date field in PowerApp

Hey @Steve_G 

 

Can you try to update your expression as:
Filter('[dbo].[AB103_CurrentEventBookings]',Text(EV803_BKG_DATE,"mm/dd/yyyy") = Text(DatePicker1.SelectedDate,"mm/dd/yyyy"))
 
Hope this helps!

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!
Super User
Super User

Re: How do I evaluate a SQL date field in PowerApp

Hi @Steve_G 

If '[dbo].[AB103_CurrentEventBookings]' is a view and it's possible for you to modify the definition, you might have more success if you Cast the EV803_BKG_DATE column to the DateTimeOffset data type. The purpose of this is to help overcome a known bug with SQL Server and date filtering.

https://powerusers.microsoft.com/t5/Building-PowerApps-Formerly/Trying-to-filter-or-lookup-on-a-date...

One issue with converting the date to text with Text(EV803_BKG_DATE,"mm/dd/yyyy") is that it results in a non-delegable query. Therefore, your results may not be correct if your source data exceeds 2000 rows.

 

 

View solution in original post

Steve_G
Level: Powered On

Re: How do I evaluate a SQL date field in PowerApp

No matter how I tweak it I can't get Text(EV803_BKG_DATE,"mm/dd/yyyy") to work. It just gives an error that 'Text or Date expected' and doesn't seem to be able to recognise EV803_BKG_DATE as a date.

Steve_G
Level: Powered On

Re: How do I evaluate a SQL date field in PowerApp

Thanks Tim,

 

I do have access to the view, so I have added an extra column of a text field with the date in yyMMdd format and then comparing it to a variable in the same format that is being Set by the date picker.

 

Bit convoluted, but it does the job and gets me moving forward, so thanks for that tip.

 

 

Helpful resources

Announcements
New Ranks and Rank Icons in April

'New Ranks and Rank Icons in April

Read the announcement for more information!

Better Together’ Contest Finalists Announced!

'Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

thirdimage

Community Summit North America

Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (4,836)