在TFS中如何使用独立字段作为偏移量实现工作项日期查询？

Great question! This is a common ask for teams looking to add dynamic date logic to their work item queries. Let’s break down your options based on how you build queries in TFS:

1. Standard Visual Query Editor #

The default drag-and-drop query editor in TFS doesn’t support using a custom field like [CustomOffset] directly as a date offset. When you set up a date condition (e.g., "Created Date is after X days ago"), you can only input a fixed numeric value—there’s no option to link it to a work item field. So if you’re relying solely on the visual editor, this won’t work out of the box.

2. WIQL (Work Item Query Language) #

If you’re comfortable writing or editing queries with WIQL (the underlying query language for TFS), you can absolutely use your [CustomOffset] field for dynamic date offsets—depending on your TFS/Azure DevOps Server version.

Example WIQL Syntax #

Suppose you want to find work items where the Due Date matches the Created Date plus the number of days specified in [CustomOffset], or where Created Date plus [CustomOffset] is before today. Here’s how you’d write those:

-- Query 1: Due Date equals Created Date plus CustomOffset days
SELECT [System.Id], [System.Title], [System.State], [CustomOffset]
FROM WorkItems
WHERE DateAdd(day, [CustomOffset], [Created Date]) = [System.DueDate]

-- Query 2: Created Date plus CustomOffset days is before today
SELECT [System.Id], [System.Title], [System.State], [CustomOffset]
FROM WorkItems
WHERE DateAdd(day, [CustomOffset], [Created Date]) < @Today

Key Notes for WIQL #

• Field Type Check: Make sure [CustomOffset] is an integer-type field (not a string or other type)—WIQL will throw an error if you try to use a non-integer here.
• Version Support: This syntax works in Azure DevOps Server 2019 and later (and Azure DevOps Services). If you’re on an older TFS version (2017 or earlier), WIQL doesn’t support using fields as the offset parameter in DateAdd, so you’ll need a workaround.

3. Workarounds for Older TFS Versions #

If you’re stuck on an older TFS version that doesn’t support field-based offsets in WIQL, try these alternatives:

• Precomputed Calculated Field: Create a calculated date field that automatically computes the offset date (e.g., DateAdd(day, [CustomOffset], [Created Date])). You can then use this calculated field in your standard visual queries. Note: Calculated fields require TFS admin access to set up.
• Scripted Queries: Use PowerShell or the Azure DevOps CLI to fetch work items, retrieve their [CustomOffset] values, and dynamically filter based on the computed date. This gives you full control but requires some scripting know-how.

Final Checks #

• Always test your WIQL queries in the TFS/Azure DevOps portal (switch to "WIQL mode" in the query editor) to validate syntax and results.
• If you’re using a local TFS server, confirm your version’s WIQL capabilities via your server’s internal help docs or release notes.

内容的提问来源于stack exchange，提问作者whoknows