.NET Core OData: How to Construct a DateTime Filter

Say, we need to construct a query to an OData endpoint that filters records by DateTime values. Over the years as OData standard had been evolving there have been various different ways to work with DateTime columns depending on OData version and an implementation of an actual OData service that could have been based on various different frameworks. This being said, it's likely, that a single universal way of constructing a DateTime based filter clause for OData services might not exist. Take this under consideration, and note that the solution presented below had only been tested with OData services that are built with .NET Web API Odata v4.

As a reminder, as for any other data type, to filter records by specific column values we'll be using an OData $filter clause.

Technically speaking there is not much difference from the syntax point of view between DateTime filtering and other data types. The comparison operations are the same: lt, lg, eq, le, and gt. The only major difference is how to supply a DateTime value to the $filter clause.

Because OData service implementation may strongly depend on an underlying framework a particular type used to represent a date and time value may vary. Because of that, in a $filter clause the DateTime value should always be presented in a standardized form known as ISO format that in general looks like this: YYYY-MM-DDThh:mm:ss.sTZD.

A DateTime value in the $filter clause should not be enclosed in ' or " otherwise it most likely will be recognized as a string. In addition, some implementations may require to cast a DateTime value in the $filter clause to a specific EDM type in order to work correctly. The last detail should be confirmed with a specific OData framework documentation.

Now, enough theory, let's look at some examples so it's all clear.

// the following examples may be equivalent
$filter=Birthday lt cast(2012-08-01T00:00:00.000Z,Edm.DateTimeOffset)

$filter=meetDate gt 2020-10-01T05:00:00.000Z

// filter records that have dates
$filter=meetDate ne null

Some OData implementations may support additional functions that can be used in the $filter clause as well:

$filter=year(VacationDate) gt 2005
$filter=month(VacationDate) ne 11
$filter=day(VacationDate) lt 8

$filter=hour(CheckInTime) eq 14
$filter=minute(CheckInTime) ge 30
$filter=second(CheckInTime) lt 59

$filter=date(Birthday) gt 1999-12-31
$filter=time(Birthday) lt 03:00:00.000

As a general rule, it's not recommended to use equality comparison when operating with DateTime values as a fraction of a second difference will render a comparison false. Try to use lt, gt, and ne operators to implement your logic unless you are dealing with precise numbers.

As a last quick tip, in JavaScript it is easy to obtain a DateTime value in ISO format using a built-in method:

const filter = '$filter=lastUpdated lt ' + filterDate.toISOString();