cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AnthonyRegnier
Helper V
Helper V

Filter SP list datetime value based on day only

Hello PA team!

 

I have a SP list with a datetime value in a column called "Check In".

I have a gallery connected to it and I simply need to exclude rows from the list that are matching the day chosen from a date picker.

 

It seems easy but nothing worked so far, I'm simply trying to do something like this:

 

Filter('Desk Reservations', DateTime(Text('Check Out', ShortDate)) <> DateTime(Text(startTime, ShortDate)))

 

Note that I have to convert to short date in order to get only the date/month/year.

 

I have tried few things like converting the date to a number of day after Jan 1st etc...

 

If you have a way, would be great!!

 

Thanks

Anthony

1 ACCEPTED SOLUTION

Accepted Solutions

@AnthonyRegnier 

So the thing is - it's all about UTC.  Your dates are going to be stored in UTC format.  You should not try to override that when you patch or you will run into issues downstream.

What typically happens with dates and SharePoint is that a date without a time is really a date with 0 for time...or 12:00 AM.

When you pull the value to compare, the timezone of your App is applied.  If your time zone adjusts negative time (i.e. a negative offset and thus, say, 10:00 PM - a two hour offset), well then, your date just changed to the prior day and it will not match/filter.

 

The first step is to see if you can get around with the date only aspect of the date and only compare that.  This will sometimes be fruitful, but it prone to issues in other time zones.  

The only real choice is to apply the TimeZoneOffset value to the date to compare and then they will compare fine.

 

HOWEVER, the Best option for dates in SharePoint (as it relates to PowerApps) is in text AND, I will add, in the yyyymmdd format only.  This statement relates to delegation.  DataSource actions that relate to dates on SharePoint are not delegable and thus will possibly give you inaccurate result depending on your list size.  By using text in that format, you can easily filter for a year, month or specific day with the StartsWith function - which is delegable.  PowerApps will let you filter on filter and so on - so, if you need a range in a period, you can use the StartsWith to get a subset of dates and then apply an additional filter on the real dates.  But then you will have a subset that is usually in the scope of the record limit...and thus delegation is not a concern.

 

So, summary - use two columns in your list.  One a Date column and the other a Text column.  Store the real date in the date column (don't adjust the time, just store as it is) and then store the date in yyyymmdd format in the text column.

 

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

3 REPLIES 3
RandyHayes
Super User III
Super User III

@AnthonyRegnier 

You might want to consider the following formula:

Filter(
    'Desk Reservations', 
    Date(Text('Check Out', ShortDate)) <> Date(Text(startTime, ShortDate))
)

In the case of SharePoint, even with Date only columns, you will want to factor out the time aspect completely. 

This still may not resolve your issue depending on how you have the TimeZone set on your SharePoint list.

If not, then you'll need to do a DateAdd to the SharePoint date in order to adjust the TimeZoneOffset.

 

I hope this is helpful for you.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
AnthonyRegnier
Helper V
Helper V

Hello,

 

Thanks a lot for your answer!

 

The patch command I use to write into the SharePoint already does take in consideration the offset value. But it's true that if the app gets used abroad, I will have to use text and not datetime type value not to suffer from the site timezone config (regional timezone somewhat doesn't override i don't understand why). 

 

The command I sent you guys does not work. Is my only choice to use text instead of datetime variable from SharePoint in order to get the filter to work? How hard can it be to just have a simple filter command that filters out same days rows?

 

Thanks 

Anthony 

@AnthonyRegnier 

So the thing is - it's all about UTC.  Your dates are going to be stored in UTC format.  You should not try to override that when you patch or you will run into issues downstream.

What typically happens with dates and SharePoint is that a date without a time is really a date with 0 for time...or 12:00 AM.

When you pull the value to compare, the timezone of your App is applied.  If your time zone adjusts negative time (i.e. a negative offset and thus, say, 10:00 PM - a two hour offset), well then, your date just changed to the prior day and it will not match/filter.

 

The first step is to see if you can get around with the date only aspect of the date and only compare that.  This will sometimes be fruitful, but it prone to issues in other time zones.  

The only real choice is to apply the TimeZoneOffset value to the date to compare and then they will compare fine.

 

HOWEVER, the Best option for dates in SharePoint (as it relates to PowerApps) is in text AND, I will add, in the yyyymmdd format only.  This statement relates to delegation.  DataSource actions that relate to dates on SharePoint are not delegable and thus will possibly give you inaccurate result depending on your list size.  By using text in that format, you can easily filter for a year, month or specific day with the StartsWith function - which is delegable.  PowerApps will let you filter on filter and so on - so, if you need a range in a period, you can use the StartsWith to get a subset of dates and then apply an additional filter on the real dates.  But then you will have a subset that is usually in the scope of the record limit...and thus delegation is not a concern.

 

So, summary - use two columns in your list.  One a Date column and the other a Text column.  Store the real date in the date column (don't adjust the time, just store as it is) and then store the date in yyyymmdd format in the text column.

 

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

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

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Users online (2,496)