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

Filter using date field from SQL Server

Hello,

 

I am filtering data on a `date` field  in the SQL server table. It goes something like this:

`Filter('[dbo].[GM_Hours]',PayDate=DataCardValue37.SelectedDate).WorkedHours`

The above result feeds into the dropdown box. However nothing shows up in the drop down and I believe the reason being the date in the SQL server does not match with the DatePicker property. 

Can anyone tell, when the date field in the SQL server table is in this format, `yyyy-mm-dd`, what format does it get converted when it is used for comparison within PowerApps.

2 REPLIES 2
Microsoft RusselThomas
Microsoft

Re: Filter using date field from SQL Server

Hi vshets,

 

 

If you have a DateTime value on either end of the equation, then you have to be sure they match down to the millisecond to receive results from an "=" condition.

 

I believe if you check DateTime against Date, I would imagine that the Date value gets converted to DateTime and the trailing minutes/seconds/milliseconds set to 00:00:00.  So if you tested Now()=Today() you'd get false unless you happened to test it at precisely midnight, on the millisecond 🙂

 

It appears you may have DateTime data in SQL and you're trying to compare it to a Date value in PowerApps?

To confirm your source format in SQL, collect some data into a collection and put some eyeballs on it;

 

 

ClearCollect(collectSQLDates, 
     ShowColumns('[dbo].[GM_Hours]',PayDate)
    )

you can then view the collection in the collections menu and see the format.

 

You also want to be cognisant of the dateformat of your datepicker/date control you're using in your datacard as this can be changed using the Format: property of the control.

 

If either one of the formats on either side of the equation are DateTime, you need to convert it to a Date, otherwise you'll always be trying to match down to the millisecond (unless you're actually trying to, which I don't think you are :))

 

You can convert a DateTime to Date using the DateValue() and Text() functions. 

 

DateValue(Text(dateTimeValue, "en-US")) 

This should spit out a date which you can reformat any way you like, but regardless of the format it will be a Date, not a DateTime and should be easier to use when comparing it with another Date.  

 

As an example, add a text label and set its Text: property to;

Now()=Today()

It should return false

 

Now set its Text: property to;

DateValue(Text(Now(), "en-US"))=Today()

Is should return true 

 

If your SQL date is DateTime and you want to filter results by SelectedDate, it could create some tricky problems when filtering - mainly delegation.  If you try and convert your DateTime values as part of the filter command, the source will not understand the DateValue() function and will return the first 500 rows of data for PowerApps to then convert locally.  Not ideal.

 

So you may be tempted to do this;

Filter('[dbo].[GM_Hours]',
DateValue(Text(PayDate, "en-US")) = 
DateValue(Text(DataCardValue37.SelectedDate, "en-US")).WorkedHours

But if you get delegation warnings, which I suspect you will, it would be better to either add another column to the source that contains a Date value based on the DateTime column and filter against that, or execute the filter using Flow and a SQL stored procedure that does the filter for you.  

 

Hope this helps,


RT

vshets
Level: Powered On

Re: Filter using date field from SQL Server

Hi RT,

 

Thanks for the explanation .. I did what you recommended and from the collections I can see that the `PayDate` column is of `Date` type (and not DateTime). Also in powerApps, the date picker field is of .ShortDate format. So in theory they should match.

I have created an additional column called PayDate_Text which converts the PayDate in the Date format to a text format.  

This is the bizaare part, when I run this:

LookUp('[dbo].[GM_Hours]',PayDate_Text = Text(DataCardValue37.SelectedDate,"[$-en-US]mm/dd/yyyy"),PayDate) = DataCardValue37.SelectedDate

results in true!!

 

 

However when I run this:

LookUp('[dbo].[GM_Hours]',PayDate = DataCardValue37.SelectedDate,PayDate) = DataCardValue37.SelectedDate

returns false. So it looks like inside the filter or lookup function, date format comparisons do not work.
Can this be true? Hope not. 

Helpful resources

Announcements
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.

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Join THE global Microsoft Power Platform event series

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

thirdimage

Microsoft Business Applications Virtual Launch

Join us for the Microsoft Business Applications Virtual Launch Event on Thursday, April 2, 2020, at 8:00 AM PST.

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 (8,202)