Edit

Query by date or current iteration in Azure Boards

  • Article

Azure DevOps Services | Azure DevOps Server 2022 - Azure DevOps Server 2019 | TFS 2018

To list work items based on when they were created, closed, resolved, or changed—you can specify a date or use a supported macro. Use the @Today macro and specify a plus or minus number of days for relative dates. For queries that list work items based on their assignment to a team's current sprint, use @CurrentIteration.

For example, you can find work items that were modified in the last three days with the following query.

Screenshot of Query Editor, Changed Date >= 3.

Screenshot of query editor, filter based on recent changes.

Also, you can use the CurrentIteration +/- _n_ macro to create queries based on a sliding window of team iterations.

Supported operators and macros

Query clauses that specify a DateTime field or the Iteration Path can use the operators and macros listed in the following table.

Data type

Supported operators and macros

DateTime

= , <> , > , < , >= , <= , =[Field], <>[Field], >[Field], <[Field], >=[Field], <=[Field], In, Not In, Was Ever

Macros: @StartOfDay, @StartOfWeek, @StartOfMonth, @StartOfYear, and @Today.
You can use +/- n with each of the supported macros.

TreePath

= , <> , Under, Not Under
Macros: @CurrentIteration2 and @CurrentIteration +/- n3 valid with the Iteration Path field.

DateTime

= , <> , > , < , >= , <= , =[Field], <>[Field], >[Field], <[Field], >=[Field], <=[Field], In, Not In, Was Ever
Macros: @Today which you can specify with +/- n integer.

TreePath

=, <> , Under, Not Under Macros: @CurrentIteration2 is valid with the Iteration Path field

Notes:

  1. The @StartOfDay, @StartOfWeek, @StartOfMonth, @StartOfYear macros are supported for Azure DevOps Server 2019.1 and later versions, and only when run from the web portal.
  2. The @CurrentIteration +/- n macro is supported for Azure DevOps Server 2019 and later versions, and only when run from the web portal.

Tip

The WasEver operator can be used with the Iteration Path field but only when defined through the WIQL syntax. For an example, see Work Item Query Language (WIQL) syntax reference.

Date and time pattern

The date and time pattern you enter for DateTime fields should match that which you select through your profile. To view or change your selection, see Set user preferences, Time and Locale.

Time and Locale page, Date pattern options Time and Locale page, Time pattern options

Time and Locale page, Date pattern options

Client restrictions on the use of the @CurrentIteration macros

You can use the @CurrentIteration macro in a query from the following clients:

  • Web portal that connects to Azure Boards
  • Visual Studio 2019 or later versions connected to Azure Boards
  • Using the REST API.

You can use the @CurrentIteration +/- n macro in a query against Azure Boards, Azure DevOps Server 2019, and later versions, and with a REST API that includes the team as a parameter, for example, @CurrentIteration('[Project]/Team').

An error occurs if you open a query that contains the @CurrentIteration macro in earlier versions of Visual Studio, or from Excel or Project. Also, you can't use the macro when copying or cloning test suites and test cases, defining alerts, or with REST APIs.

Date-based queries

You can filter for work items by the date on which they were changed or for a specific time period. Limiting the scope of your query can help with performance by only returning results that fit the date range that you include. If you're new to creating queries, see Use the query editor to list and manage queries.

Not all fields are valid for all work item types. Jump to date fields for the set of fields you can include in queries and which work item types they apply to.

Tip

Remember to enter dates in the Date Pattern you set for your personal profile.

Filter for

Include these query clauses

Items created in the last 30 days

Screenshot of query editor, Clause for finding items created in the last 30 days.

Items modified on a specific date

Screenshot of query editor, Clause for finding items changed on a specific date.

Items resolved today

Screenshot of query editor, Clause for finding items resolved today.

Items closed within a specified time period

Screenshot of query editor, Clause for finding items closed within a specified time period.

Items that haven't been closed (Closed Date is null)

Screenshot of query editor, Clause for finding items whose Closed Date is empty or null.

Items whose status was updated within the last week

Screenshot of query editor, Clause for items whose status was updated within the last week.

Items closed during the current sprint (the @CurrentIteration macro references the sprint defined for the current team context)

Screenshot of query editor, Clause for items closed during the current sprint.

Tip

To understand how AND/OR clauses are grouped, see Create and save managed queries, Group clauses. To view the WIQL syntax for a query, install the WIQL query editor extension which allows you to see the WIQL version of any query editor entry.

Create start of day, week, month, or year date-based queries

The following examples show how to use the StartOf... macros to filter for work items with various offsets. For more examples of using these macros, see WIQL syntax.

Note

Requires Azure DevOps Server 2019 Update 1 or later version.

Filter for

Include these query clauses

Bugs closed in the last two weeks

Screenshot of query editor, Clauses for finding bugs closed in the last two weeks.

Items modified in the last 10 days

Screenshot of query editor, Clause for finding items changed in the last 10 days.

Features scheduled to be completed in the next three months

Clauses for features scheduled to be completed in the next 3 months.

Not all fields are valid for all work item types. Jump to date fields for the set of fields you can include in queries and which work item types they apply to.

Create queries for your team's current iteration

If your team follows Scrum processes, you schedule work to be completed in sprints. You can track the progress of requirements, bugs, and other work to be completed in the current sprint using the @CurrentIteration macro.

Any item assigned to a sprint that corresponds to the current iteration path for the team is found. For example, if a team is on Sprint 5, the query returns items assigned to Sprint 5. Later, when the team is working in Sprint 6, the same query returns items assigned to Sprint 6.

Note

For the @CurrentIteration macro to work, the team must have selected an Iteration Path whose date range encompasses the current date. For details, see Define iteration paths (also referred to as sprints) and configure team iterations. Also, queries that contain this macro are only valid when run from the web portal.

See also Client restrictions on the use of the @CurrentIteration macros later in this article.

Azure Boards adds a team parameter when you select the @CurrentIteration or @CurrentIteration +/- n macros. The team parameter is derived from your current team context.

Query filter using the CurrentIteration macro with team parameter

Tip

If the @CurrentIteration macro isn't working, check that the expected iteration is selected for your team and that dates have been set for it.

To change the team parameter the system automatically sets, you choose it by typing the name of the team into the parameter field added below the @CurrentIteration macro.

Choose team parameter

Before creating or updating a query to use the @CurrentIteration macro, make sure you select your team. The @CurrentIteration macro references the current team selected in the web portal.

Query filter using the CurrentIteration macro

Create a sliding window of your team's iterations query

Use the @CurrentIteration +/- n macro when you want to track the work a team has planned for upcoming sprints and for understanding work that wasn't completed in previous sprints.

Note

For the @CurrentIteration +/- n macro to work, the team must have selected Iteration Paths that meet the +/- n criteria and date ranges encompass the current date for the @CurrentIteration. For details about team selection of Iteration Paths, see Define iteration (sprint) paths and configure team iterations.

See also Client restrictions on the use of the @CurrentIteration macros later in this article.

Here we show how to list all User Stories and Bugs that are assigned to the sliding window that spans the last two, the current, and the next two sprints selected for the Cloud Admin and Tools team.

CurrentIteration plus and minus clauses

To use this macro, the specified team must have selected a set of sprints that span the +/- n value entered for the macro.

List work items moved out of a sprint

You can list work items that were defined for a sprint but later moved out using a query with a clause that contains the Was Ever operator for the Iteration Path. You can only construct this query using the WIQL syntax. You can edit the WIQL syntax in the Query Editor by installing the Wiql Editor Marketplace extension.

For example, the following syntax queries for work items that meet the following criteria:

  1. Defined in the current project
  2. Work item type equals User Story or Bug
  3. Work items are under the Fabrikam Fiber Web team Area Path
  4. Work items aren't in a Closed, Completed, Cut, or Resolved state
  5. Not in the current iteration path for the Fabrikam Fiber Web team
  6. But were assigned to the current iteration path for the Fabrikam Fiber Web team
  7. Are now assigned to the Current iteration +1 for the Fabrikam Fiber Web team
  8. And were changed within the last 30 days (the length of the sprint)
SELECT
    [System.Id],
    [System.WorkItemType],
    [System.AssignedTo],
    [System.Title],
    [System.State],
    [System.Tags],
    [System.IterationPath],
    [System.AreaPath]
FROM workitems
WHERE
    [System.TeamProject] = @project
    AND [System.WorkItemType] IN ('User Story', 'Bug')
    AND [System.AreaPath] UNDER 'FabrikamFiber\Web'
    AND NOT [System.State] IN ('Completed', 'Closed', 'Cut', 'Resolved')
    AND NOT [System.IterationPath] = @currentIteration('[FabrikamFiber]\Web <id:cdf5e823-1179-4503-9fb1-a45e2c1bc6d4>')
    AND (
        EVER (
            [System.IterationPath] = @currentIteration('[FabrikamFiber]\Web <id:cdf5e823-1179-4503-9fb1-a45e2c1bc6d4>')
        )
        AND [System.IterationPath] = @currentIteration('[FabrikamFiber]\Web <id:cdf5e823-1179-4503-9fb1-a45e2c1bc6d4>') + 1
        AND [System.ChangedDate] >= @today - 30
    )
ORDER BY [System.Id]

The Query Editor view of the syntax appears as shown.

Note

The Query Editor displays a information icon next to the Was Ever operator, indicating an issue with the clause. However, the query will still run and you can create query charts. However, to modify the query, you need to use the WIQL editor.

Screenshot of Query Editor, Work Items moved out of a sprint.

List work items added to a sprint after the start date

To list newly created work items added to a sprint after its start date, use a query similar to the one shown in the following image. This query works by filtering for work items assigned to the current sprint but were created after the start of the sprint date. In this example, this is achieved with the clause created Date = @Today - 28.

Screenshot of Query Editor, Work Items newly created and added to a sprint after it's start date.

For other options for querying changes to sprint scope, see About Sprints, Scrum and project management, Sprint scope change.

Date and Iteration Path fields

You can use date fields to filter your queries. Some of these fields are populated with information as a work item progresses from one state to another. Several of these fields don't appear on the work item form, but are tracked for those work item types listed in the following table.

Field name

Description

Work item types

Activated Date (Notes 1, and 2)

The date and time when the work item was created or when its status was changed from closed, completed, or done to a new or active state.
Reference name=Microsoft.VSTS.Common.ActivatedDate, Data type=DateTime

Bug, Change Request, Epic, Feature, Issue, Product Backlog Item, Requirement, Review, Risk, Shared Step, Task, Test Case, User Story

Change Date

The date and time when a work item was modified.
Reference name=System.ChangedDate, Data type=DateTime

All

Closed Date (Note 2)

The date and time when a work item was closed.
Reference name=Microsoft.VSTS.Common.ClosedDate, Data type=DateTime

All

Created Date

The date and time when a work item was created.
Reference name=System.CreatedDate, Data type=DateTime

All

Due Date

The forecasted due date for an issue to be resolved.
Reference name=Microsoft.VSTS.Scheduling.DueDate, Data type=DateTime

Issue (Agile)

Finish Date (Note 3)

The date and time when the schedule indicates that the task will be completed.
Reference name=Microsoft.VSTS.Scheduling.FinishDate, Data type=DateTime

Requirement, Task, Test Plan, User Story

Iteration Path

Groups work items by named sprints or time periods. The iteration must be a valid node in the project hierarchy. You define iteration paths for a project and select iteration paths for a team define iteration paths for a project and select iteration paths for a team.
Reference name=System.IterationPath, Data type=TreePath

All

Resolved Date (Notes 1 and 2)

The date and time when the work item was moved into a Resolved state.
Reference name=Microsoft.VSTS.Common.ResolvedDate, Data type=DateTime

Bug, Change Request, Epic, Feature, Issue, Product Backlog Item, Requirement, Review, Risk, Shared Step, Task, Test Case, User Story

Start Date (Note 3)

The date and time when the schedule indicates that the task will start.

Note

Delivery Plans uses the Start Date and Target Date to show the span of Features, Epics, and other portfolio backlog items.

Reference name=Microsoft.VSTS.Scheduling.StartDate, Data type=DateTime

Epic, Feature, Requirement, Task, Test Plan, User Story

State Change Date

The date and time when the value of the State field changed.
Reference name=Microsoft.VSTS.Common.StateChangeDate, Data type=DateTime

All

Target Date

The date by which a feature, work item, or issue is to be completed or resolved.

Note

Delivery Plans uses the Start Date and Target Date to show the span of Features, Epics, and other portfolio backlog items.

Reference name=Microsoft.VSTS.Scheduling.TargetDate, Data type=DateTime

Epic, Feature

Notes:

  1. See also Query by assignment or workflow changes, Date, and Identity fields.

  2. For these fields to be defined for a WIT, they must be included in the WORKFLOW section of the WIT definition. For example, this syntax is included within the FIELDS definition when transitioning to a Resolved state:

     <FIELD refname="Microsoft.VSTS.Common.ResolvedDate" />  
    <SERVERDEFAULT from="clock"  />  
 </FIELD >

  3. Start Date and Finish Date fields are calculated if you create a project plan in Microsoft Project and then synchronize that plan with tasks that are stored in Azure Boards. These fields might not appear on the work item form, but are calculated for the backlog items and tasks that are linked to backlog items. You can view their read-only values in results from a query or from Microsoft Excel.

    Important

    Microsoft Project Integration and the TFSFieldMapping command are not supported for:

    • Visual Studio 2019 and Azure DevOps Office® Integration 2019
    • Azure DevOps Server 2019 and later versions, including Azure DevOps Services.

    However, full support for Microsoft Excel integration is maintained and supports bulk import and update of work items. Alternatives to using Microsoft Project include the following:

To query for items based on text entered in the History field, see History and auditing.

REST API

To programmatically interact with queries, see one of these REST API resources: