Sharepoint Date Query

(0) Share

Report

Posted on by Screamingcities

Hi All,

So I'm attempting to make a query between two dates.

In the problematic query it's anything that falls on or between 11/10/2021 and 11/17/2021

Both have the same creation time on SharePoint (maybe off by a few seconds)

When I filter by date in PowerApps one entry shows.. the other only shows if you set the end date one day later (11/18/2021).

Here's my code:

Filter(

'Reporting Updates',

assigned_agent.DisplayName = currentAnalyst And due_date >= startDate And due_date <= endDate

)

I thought maybe it was a UTC vs Local thing... But both of them show the exact same time/date string

What am I missing?

Categories:

Building Power Apps

I have the same question (0)

All responses (9)

Answers (0)

Sort by

Pstork1 69,657 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

What happens if you remove the currentAnalyst portion of the filter? or do just >= startDate and then <= endDate. I would try breaking the filter down into smaller pieces to see if the two both show up in all the individual filters.

Was this reply helpful? Yes No
Screamingcities 9 on at

Like
a
(0)

Report
Copy link

Link copied!

Nope... Even if I remove the currentAnalyst portion it still only finds it if I set the date one day ahead.

(I think it might have to do with the SharePoint item actually. I just tried linking it to an Access DB and querying it there... Same exact thing happens.)

Was this reply helpful? Yes No
Pstork1 69,657 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

I still think you are running into a UTC issue. What are the full Date and time values for the two items that should show up?

Was this reply helpful? Yes No
Screamingcities 9 on at

Like
a
(0)

Report
Copy link

Link copied!

4750 Wednesday, November 17, 2021 0:00:00 (This is the one that shows)

8099 Wednesday, November 17, 2021 0:00:00 (This is the one that only shows if I set the end date to the 18th)

(4750 and 8099 are the ID numbers)

Was this reply helpful? Yes No
Pstork1 69,657 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

So if 8099 is an ID number how big is the list? Could you be running into SharePoint's 5,000 item limit on a view? This is different from delegation.

Was this reply helpful? Yes No
Screamingcities 9 on at

Like
a
(0)

Report
Copy link

Link copied!

So this was weird.

Background- the List in question has a Date/Time field that is set to Date only. The Date was copied from a list that was set to show both Date and Time.

So apparently even though PA was reporting that the timestamp was empty, it was also using the original timestamp from the copied item.

I feel like SharePoint shouldn't do that...

Was this reply helpful? Yes No
Pstork1 69,657 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!

SharePoint Date fields get stored as a full Date/Time field. Date Only fields truncate it to just the date when you retrieve the field. But of course it stores it in UTC. So yes, I believe its because the original date had a time other than midnight. I also agree it would be good if it didn't misreport the value. But, I believe you have found your issue.

Was this reply helpful? Yes No
Screamingcities 9 on at

Like
a
(0)

Report
Copy link

Link copied!

Yeah I feel like it's some overlooked error, but maybe there's some sort of reasoning for the misreporting I'm not aware of. ¯\_(ツ)_/¯

Anyway thanks for your help!

Was this reply helpful? Yes No
Pstork1 69,657 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

Its because it used to actually report both date and time for a date only field. There were complaints so MS fixed the issue by truncating what is returned to just the date. But they didn't change the backend storage. For dates entered directly it works fine. For Dates that are brought in as Date Time there is the chance to get an error as you are seeing. Its because the fix is a retrofit that has a fairly obscure issue. Most DateOnly fields will work fine.

Was this reply helpful? Yes No