cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

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
RusselThomas
Microsoft
Microsoft

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

Anonymous
Not applicable

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
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Carousel_PP_768x460_Wave2 (1).png

2022 Release Wave 2 Plan

Power Platform release plan for the 2022 release wave 2 describes all new features releasing from October 2022 through March 2023.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Top Solution Authors
Users online (3,129)