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
Super User

@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
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,614)