Filter SP list datetime value based on day only

[AnthonyRegnier]

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

[RandyHayes]

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

[AnthonyRegnier]

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 

[RandyHayes · Accepted solution]

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