Answered

odata filter query - equals today's date

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Screenshot 2020-11-18 120024.png I'm using SharePoint Get Items and applying a filter query there. I want to only retrieve items where the date in the list (Outward_x0020_date) is equal to today's date.

I've tested it several times with different queries but Outward_x0020_date eq formatDateTime(utcNow(), 'dd/MM/yyyy') is the one I think should work. However I get the error The expression "Outward_x0020_date eq 18/11/2020" is not valid. Creating query failed. I've also tried putting single ' ' around the date but that also had an error.

It must be something to do with the date format, but this is the format used in the SP list. Any ideas?

Categories:

Using connectors

I have the same question (0)

All responses (4)

Answers (1)

Sort by

Expiscornovus 33,995 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @Anonymous,

My guess would be that it is expecting time as well. Can you try and use this expression to see if will make your expression valid?

Outward_x0020_date eq '@{ formatDateTime(utcNow(),'dd/MM/yyyyT00:00:00Z')}'

Btw, can you also check the column settings of your Outward date column? If it is using time I would recommend changing that, if possible. Because if you strip away the time the Filter query can become a bit easier:

Outward_x0020_date eq '@{ formatDateTime(utcNow(),'dd/MM/yyyy')}'

Was this reply helpful? Yes No
Verified answer

Community Power Pla... Microsoft Employee on at

Like
a
(5)

Report
Copy link

Link copied!

Thanks for the suggestions @Expiscornovus . This didn't work as my column doesn't include time. However, I've worked out that even though the format of the date in my SP list is dd/MM/yyyy, the date appears as 'yyyy-MM-dd'.
So once I set my query to:
formatDateTime(utcNow(), 'yyyy-MM-dd') it worked.

Was this reply helpful? Yes No
Expiscornovus 33,995 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

@Anonymous, great to hear that you found your solution. And happy to help 🙂

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

You can use this date format in this
formatDateTime(utcNow(), 'M/d/yyyy')

DATE field with default formatting: 5/16/2023

The following table demonstrates some custom date/time formats. The left column shows the field
code, right column contains the actual field using that code.
DATE \@ "M/d/yyyy" 5/16/2023
DATE \@ "dddd, MMMM dd, yyyy" Tuesday, May 16, 2023
DATE \@ "MMMM d, yyyy" May 16, 2023
DATE \@ "M/d/yy" 5/16/23
DATE \@ "yyyy-MM-dd" 2023-05-16
DATE \@ "d-MMM-yy" 16-May-23
DATE \@ "M.d.yyyy" 5.16.2023
DATE \@ "MMM. d, yy" May. 16, 23
DATE \@ "d MMMM yyyy" 16 May 2023
DATE \@ "MMMM yy" May 23
DATE \@ "MMM-yy" May-23
DATE \@ "M/d/yyyy h:mm am/pm" 5/16/2023 12:20 PM
DATE \@ "M/d/yyyy h:mm:ss am/pm" 5/16/2023 12:20:51 PM
DATE \@ "h:mm am/pm" 12:20 PM
DATE \@ "h:mm:ss am/pm" 12:20:51 PM
DATE \@ "HH:mm" 12:20
DATE \@ "'Today is 'MMMM d, yyyy" Today is May 16, 2023

DateAndTime.pdf

Was this reply helpful? Yes No