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 II
Advocate II

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!

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power Apps Community Call Jan. 2022 768x460.png

Power Apps Community Call

Please join us on Wednesday, January 19th, at 8a PDT. Come and learn from our amazing speakers!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

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