Filter with date (ODATA) and add date formatting

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


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


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)


Let’s take it even further and add 5 days to the date


Or remove 5 days


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

See also