Learn how to use the ODATA to filter on date columns in SharePoint, and how to format your date
When you are using the Get items from SharePoint, you can use ODATA to filter your query.
Here are some examples of how to filter and format date fields
Created today
When a new item is created in SharePoint the internal name for the creation date is Created
It holds the date and time
If we want to get all the items that have been created today, we need to find all the items that have been created between 00:01 and 23:59:59
The ODATA expression is
Created ge 'expression1' and Created le 'expression2'
Replace expression1 with formatDateTime(utcNow(), 'yyyy-MM-ddT00:00:01Z')
Replace expression2 with formatDateTime(utcNow(), 'yyyy-MM-ddT23:59:59Z')
The expression should be placed in between the ' ' signs
Convert time zone
To convert the time to a local timezone, use convertTimeZone()
This expression converts the timezone to my local time zone in Denmark
formatDateTime(convertTimeZone(utcNow(), 'UTC', 'W. Europe Standard Time'), 'yyyy-MM-ddTHH:mm:ssK')
You own date field
If you have created your own date field (without time), the expression is a bit more simple
Here I filter a date field with the internal name MyDate
MyDate eq formatDateTime(utcNow(), 'yyyy-MM-dd')
Get items X days ago
To substract 4 days from a date field, use following expression
formatDateTime(addDays(utcNow(),-4),'yyyy-MM-dd')
Here is the ODATA filter
MyDate eq 'expression1'
Replace expression1 with formatDateTime(addDays(utcNow(),-4),'yyyy-MM-dd')
The expression should be placed in between the ' ' signs
Simply remove the - to add 4 days
Formatting a date field
In SharePoint/Power Automate my field MyDate has the following formatting
"MyDate": "2024-05-31
To change the format from a single item (Get item) to 31-05-2024 instead, use the following expression
formatDateTime(outputs('Get_item')?['body/MyDate'],'dd-MM-yyyy')
To change the format from multiple items (Get items) use the following expression (this will change the date from the first item in the array)
formatDateTime(outputs('Get_items')?['body/value']?[0]?['MyDate'],'dd-MM-yyyy')
Let’s take it even further and add 5 days to the date
formatDateTime(addDays(outputs('Get_items')?['body/value']?[0]?['MyDate'],5),'dd-MM-yyyy')
Or remove 5 days
formatDateTime(addDays(outputs('Get_items')?['body/value']?[0]?['MyDate'],-5),'dd-MM-yyyy')
Empty date field
If you are using a date field in an email and want to display the date in a specific format, but the user has not provided a date, you will encounter an error. Here is how to handle an empty date
if(empty(triggerOutputs()?['body/MyDate']), null, formatDateTime(triggerOutputs()?['body/MyDate'],'dd-MM-yyyy'))
Above expression is from triggerOutputs() (the trigger that started your flow)
If you want to use a field from any other outputs, the expression should hold the name of that action (outputs)
if(empty(outputs('get_item')?['body/MyDate']), null, formatDateTime(outputs('Get_item')?['body/MyDate'],'dd-MM-yyyy'))
ODATA filters
Here is a list of how you use the less than, equal to etc. in the ODATA filter:
- Equal To: eq
- Example: field eq value
- Not Equal To: ne
- Example: field ne value
- Greater Than: gt
- Example: field gt value
- Greater Than or Equal To: ge
- Example: field ge value
- Less Than: lt
- Example: field lt value
- Less Than or Equal To: le
- Example: field le value
- Logical AND: and
- Example: field1 eq value1 and field2 eq value2
- Logical OR: or
- Example: field1 eq value1 or field2 eq value2
- Grouping with Parentheses: ( )
- Example: (field1 eq value1 or field2 eq value2) and field3 eq value3