[Bug]: smalldatetime column cannot be filtered via OData $filter - three literal forms, three different failurs

[Imlbrown]

What happened?

Filtering a smalldatetime column through the MCP DML tools fails for every literal form tried. Three forms were tested and each failed differently: one is rejected by the parser, one fails at parameter binding, and one fails with no type mapping. No form succeeded.

In the parameter-binding case the query itself is correct — DAB resolves the entity and fields and emits well-formed SQL with correct parameter placeholders. The failure occurs when binding the parameter value.

This was against on-premise SQL Server 2022.

Version

2.0.9

What database are you using?

Azure SQL

What hosting model are you using?

Custom Docker host

Which API approach are you accessing DAB through?

MCP

Relevant log output

### 1. `aggregate_records`, ISO 8601 with `Z` — `UnexpectedError`

Arguments:


{
  "entity": "ARAS_Charges_By_Day",
  "function": "count",
  "field": "*",
  "filter": "ChargeNumber eq '390100' and DateOfWork ge 2026-07-01T00:00:00Z and DateOfWork lt 2026-08-01T00:00:00Z",
  "groupby": ["Username"]
}


SQL generated (correct):


SELECT TOP 1001 [dbo_ARAS_LABOR_CHARGES_BY_DAY_NO_DATE_RESTRICT].[Username] AS [Username],
       count([dbo_ARAS_LABOR_CHARGES_BY_DAY_NO_DATE_RESTRICT].[DateOfWork]) AS [count]
FROM [dbo].[ARAS_LABOR_CHARGES_BY_DAY_NO_DATE_RESTRICT] AS [dbo_ARAS_LABOR_CHARGES_BY_DAY_NO_DATE_RESTRICT]
WHERE ((([ChargeNumber] = @param0) AND ([DateOfWork] >= @param1)) AND ([DateOfWork] < @param2))
GROUP BY [dbo_ARAS_LABOR_CHARGES_BY_DAY_NO_DATE_RESTRICT].[Username]
ORDER BY COUNT([dbo_ARAS_LABOR_CHARGES_BY_DAY_NO_DATE_RESTRICT].[DateOfWork]) DESC
FOR JSON PATH, INCLUDE_NULL_VALUES


Server log:


fail: Azure.DataApiBuilder.Mcp.BuiltInTools.AggregateRecordsTool[0]
      Unexpected error in AggregateRecordsTool.
      System.InvalidCastException: Failed to convert parameter value from a DateTimeOffset to a DateTime.
       ---> System.InvalidCastException: Object must implement IConvertible.
         at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvider provider)
         at Microsoft.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType destinationType, Boolean& coercedToDataFeed, Boolean& typeChanged, Boolean allowStreaming)
         --- End of inner exception stack trace ---
         at Azure.DataApiBuilder.Core.Resolvers.QueryExecutor`1.ExecuteQueryAgainstDbAsync[TResult](...) in /_/src/Core/Resolvers/QueryExecutor.cs:line 296
         at Azure.DataApiBuilder.Core.Resolvers.QueryExecutor`1.ExecuteQueryAsync[TResult](...) in /_/src/Core/Resolvers/QueryExecutor.cs:line 196
         at Azure.DataApiBuilder.Mcp.BuiltInTools.AggregateRecordsTool.ExecuteAsync(JsonDocument arguments, IServiceProvider serviceProvider, CancellationToken cancellationToken) in src\Azure.DataApiBuilder.Mcp\BuiltInTools\AggregateRecordsTool.cs:line 269
warn: Azure.DataApiBuilder.Mcp.BuiltInTools.AggregateRecordsTool[0]
      MCP Tool error UnexpectedError: Unexpected error occurred in AggregateRecordsTool.


Returned to the client:


{ "toolName": "aggregate_records", "status": "error",
  "error": { "type": "UnexpectedError", "message": "Unexpected error occurred in AggregateRecordsTool." } }


### 2. `read_records`, ISO 8601 without offset — `BadRequest`

Arguments:


{
  "entity": "ARAS_Charges_By_Day",
  "select": "Username,DateOfWork",
  "filter": "ChargeNumber eq '390100' and DateOfWork ge 2026-07-01T00:00:00 and DateOfWork lt 2026-08-01T00:00:00"
}


Returned to the client:


{ "toolName": "read_records", "status": "error",
  "error": { "type": "BadRequest",
    "message": "The DateTimeOffset text '2026-07-01T00:00:00' should be in format 'yyyy-MM-ddThh:mm:ss('.'s+)?(zzzzzz)?' and each field value is within valid range." } }


So the parser requires the offset that case 1 supplies — and that offset is what case 1 then fails to bind.

### 3. Date-only literal — no type mapping

Using an unquoted date with no time component (`DateOfWork ge 2026-07-01`), the error returned was:


No mapping exists from object type Microsoft.OData.Edm.Date to a known managed provider native type.


## Summary

| Literal form | Result |
|---|---|
| `2026-07-01T00:00:00Z` | `UnexpectedError` — `InvalidCastException`, `DateTimeOffset` → `DateTime` at `SqlParameter.CoerceValue` |
| `2026-07-01T00:00:00` | `BadRequest` — parser requires an offset |
| `2026-07-01` | No mapping from `Microsoft.OData.Edm.Date` to a provider native type |

## Expected behaviour

A `smalldatetime` column should be filterable. The parsed OData value should be converted to the CLR type matching the target column before binding.

Additionally, the case 1 failure reaches the MCP client as a bare `UnexpectedError` with no detail. If this is a value-conversion problem it is user-correctable, and a `BadRequest` naming the field and expected format would let a calling agent self-correct rather than retry blindly.

## Possibly related

- #2783 — `datetime2` columns are emitted in the generated OpenAPI as `string` with no format, and the reporter hit trouble applying date filters.
- #2268 — `SqlClient` coercion failure on date parameters (GraphQL/MSSQL). Different endpoint, same coercion layer.

## Workaround in use

An `int` day key on the view, filtered instead of the date column:


CONVERT(int, CONVERT(char(8), DateOfWork, 112)) AS DateKey
-- DateKey ge 20260701 and DateKey lt 20260801


---

### Notes before posting — delete this section

- **Case 3 has no captured payload.** Paste the exact error response and the tool/arguments used, or drop the section — the message above is transcribed from console output and the object type name may not be exact.
- **Only the two tools above were tested.** No REST `$filter` result and no `datetime2` comparison are claimed here. If you run either, add it; if a `datetime2` column filters cleanly, say so explicitly, as that narrows the cause considerably.
- **Object names are your production ones.** Rename the entity, view, and `ChargeNumber` value if you'd rather not disclose them, and check any attached log for connection details.
- **Confirm the version string** with `docker run --rm <image>:<tag> --version`, and check `compare/v2.0.9...main` for PRs #3600 / #3601 / #3619 in case this is already addressed upstream.

Code of Conduct

• I agree to follow this project's Code of Conduct