cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JRaasumaa
Memorable Member
Memorable Member

Comparing SQL DateTime column to PowerApps Dropdown

Hello,

 

I have a SQL table with a week start column as datetime that I'd like to be able to filter data into PowerApps based on a drop down with week start date values in it.

 

Ideally I'd like to use the selected dropdown as so:

Lookup(Collection, WeekStart = DropDown.Selected.Value, ReturnValue)

 

The problem is formatting the DropDown selected value seems to be impossible to match with the WeekStart SQL datetime column.

 

Any thoughts? Has anyone had success in taking a field value (dropdown.selected.value) and formatting it to be used in a filter to a SQL datetime column?

1 ACCEPTED SOLUTION

Accepted Solutions

I just found the solution to my own issue!

 

PowerApps cannot run comparisons on the datetime data type in SQL, but apparently it works fine comparing to datetimeoffset.

 

I've replaced the datetime column with a datetimeoffset and am now able to compare date values straight from that SQL column without issue.

 

In order to compare a PowerApps field with a date to the datetimeoffset in SQL you have to use DateValue(Text(FieldtoCompare, DateTimeFormat.UTC)) and then you can run a "is it equal to" on them.

 

So it is possible to make a dropdown filter a data set with PowerApps using SQL as a backend, just need to have the right data type setup!

View solution in original post

3 REPLIES 3
VincentM
Advocate III
Advocate III

Try using the date() function, you can even input a specific format as indicated in this reference:

 

https://powerapps.microsoft.com/en-us/tutorials/function-date-time/

I've tried using the date function as well but its syntax wants 3 values, year, month, day and SQL's datetime data type is a combination of all of that plus the time.

 

What's strange is I can use a Distinct lookup to the SQL datetime column and then DateValue on that and return a nice neat date from SQL, meaning it's getting the datetime value read correctly from the datetime data type, but when I try to compare dates directly to datetime columns it just doesn't work.

 

This works great in my dropdown and pulls from SQL:

Distinct(SQLDateTimeColumn,DateValue(Text(SQLDateTimeColumn,"[$-en-US]mm/dd/yyyy")))

 

I've also tested with the date picker and you can easily compare dates to the returned Distinct dropdown value and get a true/false back like this:

 

DateValue(DropDown.Selected.Value)=DatePicker1.SelectedDate

 

So it knows whether the dates are equal or not, but for whatever reason comparing a date directly to a SQL datetime similar to a "WHERE" statement in SQL it doesn't work.

 

Very frustrating!

 

 

I just found the solution to my own issue!

 

PowerApps cannot run comparisons on the datetime data type in SQL, but apparently it works fine comparing to datetimeoffset.

 

I've replaced the datetime column with a datetimeoffset and am now able to compare date values straight from that SQL column without issue.

 

In order to compare a PowerApps field with a date to the datetimeoffset in SQL you have to use DateValue(Text(FieldtoCompare, DateTimeFormat.UTC)) and then you can run a "is it equal to" on them.

 

So it is possible to make a dropdown filter a data set with PowerApps using SQL as a backend, just need to have the right data type setup!

Helpful resources

Announcements
Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Top Kudoed Authors
Users online (1,472)