What is the format for OData filter query date?

In OData filter queries, dates and times are typically represented using the ISO 8601-compliant format, specifically yyyy-MM-ddTHH:mm:ssZ.

Understanding OData Date and Time Filtering

OData (Open Data Protocol) provides a standardized way to query and manipulate data over the web. When filtering data based on date and time fields, it's crucial to use a consistent and unambiguous format. The standard format ensures correct interpretation by the OData service, preventing errors and ensuring accurate data retrieval.

The Standard OData Date-Time Format

The primary format for representing date and time values in OData filter queries is based on the ISO 8601 standard, specifically:

yyyy-MM-ddTHH:mm:ssZ

This format denotes a specific point in time, always expressed in Coordinated Universal Time (UTC), which is indicated by the trailing Z.

Breakdown of the Date-Time Components

Understanding each part of the format is key to constructing correct queries:

Component	Description	Example
`yyyy`	Four-digit year	`2023`
`MM`	Two-digit month (01-12), with a leading zero if needed	`04`
`dd`	Two-digit day of the month (01-31), with a leading zero	`15`
`T`	Time separator (a literal 'T' character)	`T`
`HH`	Two-digit hour (00-23), with a leading zero	`09`
`mm`	Two-digit minute (00-59), with a leading zero	`30`
`ss`	Two-digit second (00-59), with a leading zero	`00`
`Z`	UTC indicator (a literal 'Z' character)	`Z`

Practical Examples of OData Date Filters

Here are some common examples demonstrating how to use the yyyy-MM-ddTHH:mm:ssZ format in OData filter queries:

Filter for a specific date and time:
&$filter=EventDateTime eq 2023-04-15T09:30:00Z
Filter for records created after a certain date:
&$filter=CreationDate gt 2022-01-01T00:00:00Z
Filter for records modified before a specific time:
&$filter=LastModified lt 2023-10-26T14:00:00Z
Filter within a date range (inclusive):
&$filter=OrderDate ge 2023-03-01T00:00:00Z and OrderDate le 2023-03-31T23:59:59Z

Important Considerations for Date Filtering

When working with OData date filters, keep the following points in mind for robust and accurate queries:

UTC Standard: Always provide date and time values in Coordinated Universal Time (UTC). If your local time is different, convert it to UTC before constructing the query. This prevents ambiguities and ensures consistent results across different geographic locations.
Precision: While yyyy-MM-ddTHH:mm:ssZ is the most common format, some OData services may support higher precision, such as fractional seconds (e.g., yyyy-MM-ddTHH:mm:ss.SSSZ). Always refer to the specific service's documentation for exact precision support.
Time Zones: The Z suffix explicitly indicates UTC. Avoid using offsets like +HH:mm or -HH:mm in OData filter queries, as the Z (Zulu time) is the standard for server-side processing.
Date-Only Filters: If you only need to filter by date without a specific time, you can represent the beginning of the day using yyyy-MM-ddT00:00:00Z. For the end of a day, use yyyy-MM-ddT23:59:59Z.
Service-Specific Behavior: While the ISO 8601 format is a widely adopted standard, it's always good practice to consult the specific OData service's documentation. They might have minor variations, support additional date functions (like date(), year(), month()), or handle null date values in a particular way.

Common OData Filter Operators for Dates

These operators are typically used in conjunction with the date-time string in your filter expression:

eq: Equal to
ne: Not equal to
gt: Greater than
ge: Greater than or equal to
lt: Less than
le: Less than or equal to

These operators allow for flexible filtering based on time points, ranges, and comparisons.

Ora