MCP tools should auto-quote unquoted DateTimeOffset values in OData filter expressions

[rk-vinay]

MCP tools should auto-quote unquoted DateTimeOffset values in OData filter expressions

Summary

When AI agents (GPT-4o, GPT-4.1, GPT-5.4) call MCP DML tools (read_records, aggregate_records) with OData filter expressions containing DateTimeOffset values, every tested model consistently fails to wrap date values in single quotes, causing a BadRequest error from the OData parser.

Since MCP tools are specifically designed for AI agent consumption, and this is a universal behavior across all LLMs tested, DAB should normalize unquoted DateTimeOffset values in the filter parameter before passing them to the OData parser.

---

Reproduction Steps

Environment

• DAB Version: 2.0.1-rc (Docker image on Azure Container Apps)
• Database: Azure SQL Database (MSSQL)
• MCP Transport: Streamable HTTP
• AI Platform: Azure AI Foundry Agent Service
• Models Tested: GPT-4o, GPT-4.1, GPT-5.4

Step 1: Configure DAB with a table containing DateTimeOffset columns

{
  "entities": {
    "dlytrn": {
      "source": {
        "object": "dbo.dlytrn",
        "type": "table"
      },
      "permissions": [
        {
          "role": "anonymous",
          "actions": [{ "action": "read" }]
        }
      ]
    }
  }
}

The dlytrn table has a trndte column of type DateTimeOffset.

Step 2: AI Agent calls read_records with a date filter

Every AI model we tested constructs the filter without single quotes around the date value:

{
  "method": "tools/call",
  "params": {
    "name": "read_records",
    "arguments": {
      "entity": "dlytrn",
      "filter": "trndte ge 2026-05-29T00:00:00Z and trndte lt 2026-05-30T00:00:00Z",
      "first": 50
    }
  }
}

Step 3: DAB returns BadRequest error

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

---

All Error Examples (3 Models Tested)

GPT-4o (gpt-4o-2024-11-20)

Call 1: Agent sends exact timestamp from aggregate_records result:

{
  "entity": "dlytrn",
  "select": "*",
  "filter": "trndte eq 2026-05-28T17:25:26",
  "first": 50,
  "after": ""
}

Error: "The DateTimeOffset text '2026-05-28T17:25:26' should be in format 'yyyy-mm-ddThh:mm:ss('.'s+)?(zzzzzz)?' and each field value is within valid range."

Additional issues: Uses select: "*" (also invalid), uses eq instead of ge, passes empty after: "", invents column names (stock_code, quantity, activity_type).

---

GPT-4.1 (gpt-4.1)

Call 2: Agent uses correct column names but still fails to quote dates:

{
  "entity": "dlytrn",
  "select": "trntyp,trndte,prdcd,qty,loccd,refno",
  "filter": "trndte ge 2026-05-29T00:00:00 and trndte le 2026-05-29T23:59:59",
  "orderby": ["trndte asc"],
  "first": 20,
  "after": ""
}

Error: Same DateTimeOffset format error.

Improvement over GPT-4o: Uses ge/le instead of eq, uses T00:00:00 format. But still does not quote dates.

---

GPT-5.4 (gpt-5.4)

Call 3: Best model — uses describe_entities for discovery, correct column names, but still fails to quote dates:

{
  "entity": "dlytrn",
  "select": "dlytrn_id,wh_id,client_id,trndte,acttyp,actcod,prtnum,stoloc,untqty,usr_id",
  "filter": "trndte ge 2026-05-29T00:00:00Z and trndte lt 2026-05-30T00:00:00Z",
  "orderby": ["trndte desc"],
  "first": 50,
  "after": ""
}

Error: Same DateTimeOffset format error.

Improvement over GPT-4.1: Uses Z timezone suffix, correct column names from discovery, proper date range. But still does not quote dates.

---

What the AI Agent Sends vs What OData Expects

Item	Agent Sends (WRONG)	OData Expects (CORRECT)
Single date	trndte ge 2026-05-29T00:00:00Z	trndte ge '2026-05-29T00:00:00Z'
Date range	trndte ge 2026-05-29T00:00:00Z and trndte lt 2026-05-30T00:00:00Z	trndte ge '2026-05-29T00:00:00Z' and trndte lt '2026-05-30T00:00:00Z'
With timezone	trndte ge 2026-05-29T00:00:00+00:00	trndte ge '2026-05-29T00:00:00+00:00'
No timezone	trndte ge 2026-05-29T00:00:00	trndte ge '2026-05-29T00:00:00'

The only difference is single quotes around the date value. Every other aspect of the expression is valid OData.

---

Prompt Engineering Attempts (All Failed)

We made 6 extensive prompt engineering attempts to fix this via system prompt instructions:

Attempt	Strategy	Result
1	"Always quote dates in filters"	❌ Agent ignores instruction
2	"Dates are STRINGS in OData" with examples	❌ Agent ignores instruction
3	Exact JSON template showing correct format	❌ Agent ignores instruction
4	Few-shot example with correct and incorrect calls	❌ Agent ignores instruction
5	"NEVER" + "WILL FAIL" warning language	❌ Agent ignores instruction
6	Combined all strategies	❌ Agent ignores instruction

Conclusion: This is a fundamental behavior across all LLMs — they treat dates as typed values (like numbers) rather than string literals in filter expressions. No amount of prompt engineering resolves this reliably.

---

Root Cause in DAB Source Code

In ReadRecordsTool.cs, the filter string is passed directly to the OData parser without any preprocessing:

// Line ~150 in ReadRecordsTool.cs
if (!string.IsNullOrWhiteSpace(filter))
{
    string filterQueryString = $"?{RequestParser.FILTER_URL}={filter}";
    context.FilterClauseInUrl = sqlMetadataProvider
        .GetODataParser()
        .GetFilterClause(filterQueryString, $"{context.EntityName}.{context.DatabaseObject.FullName}");
}

There is no validation or normalization of the filter expression before parsing. When the OData parser encounters an unquoted DateTimeOffset value, it fails with a format error.

---

Affected Workflows

Any AI agent query involving dates will fail:

• "Show today's warehouse activity" → read_records with date filter → FAILS
• "Show this month's orders" → read_records with date range → FAILS
• "How many picks were done yesterday?" → aggregate_records with date filter → FAILS
• "Show receiving activity for last week" → read_records with date range → FAILS

---

Related Issues

Issue	Relationship
#1358 - DateTimeOffset type not supported	✅ Fixed — DAB can now handle DateTimeOffset columns. This issue is about filter parsing.
#1637 - Datetime types incorrectly handled for MsSql	✅ Fixed — DAB now properly distinguishes date types. Different issue.
#2268 - DateTimeOffset without offset assumes local timezone	⚠️ Open — Related. If normalization is added, timezone handling should also be considered.
#1610 - Add support for DateOnly type	⚠️ Open — Date type handling improvements. Complementary.
#3506 - Ensure MCP handles parameters properly	✅ Fixed — MCP parameter metadata. Did not address filter value normalization.

---

Environment Details

• DAB: 2.0.1-rc (Docker: mcr.microsoft.com/azure-databases/data-api-builder:latest)
• Deployment: Azure Container Apps
• Database: Azure SQL Database (MSSQL)
• MCP Transport: Streamable HTTP (/mcp endpoint)
• AI Platform: Azure AI Foundry Agent Service
• Models Tested: GPT-4o (2024-11-20), GPT-4.1, GPT-5.4
• AI Search: Azure AI Search (Knowledge grounding, Hybrid + Semantic)

---

[rk-vinay]

@Souvik04 @anniruddh , @aaronburtle Please have a look

[JerryNixon]

I wonder if this could be solved using explicit instructions?

filter must be a valid OData filter expression. When filtering date, datetime, or DateTimeOffset 
columns, always wrap literal values in single quotes.

Correct:
trndte ge '2026-05-29T00:00:00Z' and trndte lt '2026-05-30T00:00:00Z'

Incorrect:
trndte ge 2026-05-29T00:00:00Z and trndte lt 2026-05-30T00:00:00Z

[JerryNixon]

Another approach, and not a bad one might be a structured filter.

{
  "entity": "dlytrn",
  "where": [
    {
      "column": "trndte",
      "operator": "ge",
      "value": "2026-05-29T00:00:00Z"
    },
    {
      "column": "trndte",
      "operator": "lt",
      "value": "2026-05-30T00:00:00Z"
    }
  ],
  "first": 50
}

This would need a little research.

[rk-vinay]

I wonder if this could be solved using explicit instructions?

filter must be a valid OData filter expression. When filtering date, datetime, or DateTimeOffset 
columns, always wrap literal values in single quotes.

Correct:
trndte ge '2026-05-29T00:00:00Z' and trndte lt '2026-05-30T00:00:00Z'

Incorrect:
trndte ge 2026-05-29T00:00:00Z and trndte lt 2026-05-30T00:00:00Z

Hi Jerry,

Tested with multiple instructions being forced, Nothing is being respected. Most models just ignore or default to their trained action.