Query by date or current iteration

Azure DevOps Services | Azure DevOps Server 2019 | TFS 2018 | TFS 2017 | TFS 2015 | TFS 2013

The @Today and @CurrentIteration macros are useful for listing work items based on relative dates or their assignment to a team's current iteration. To list work items based on when they were created, closed, resolved, or changed state—use @Today or specify 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 3 days with the following query.

Editor query filter based on recent changes

In addition, 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; each of these macros can be specified with a +/- n interger.

TreePath

= , <> , Under, Not Under

Macros: @CurrentIteration1 and @CurrentIteration +/- n2 valid with the Iteration Path field

Data type

Supported operators and macros

DateTime

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

Macros: @Today, @Today +/- n are valid with any DateTime field

TreePath

= , <> , Under, Not Under

Macros: @CurrentIteration1 and @CurrentIteration +/- n2 valid with the Iteration Path field

Notes:

  1. The @CurrentIteration macro is supported for TFS 2015 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.

Date based queries

You can filter for work items by the date on which they were changed or for a specific time period. If you limit the scope of your query, it can help with performance by only returning those results that fit the date range that you want to 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 (WITs). Jump to date fields for the set of fields you can include in queries and which WITs they apply to. Enter dates in the Date Pattern you set for your personal profile. (See Set personal preferences for details.)

Filter for

Include these query clauses

Items created in the last 30 days

Clause for finding items created in the last 30 days

Items modified on a specific date

Clause for finding items changed on a specific date

Items resolved today

Clause for finding items resolved today

Items closed within a specified time period

Clause for finding items closed within a specified time period

Items whose status was updated within the last week

Clause for finding 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)

Clause for finding items closed during the current sprint

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 additional examples for using these macros, see WIQL syntax.

Filter for

Include these query clauses

Bugs closed in the last 2 weeks

Clauses for finding bugs closed in the last two weeks

Items modified in the last 10 days

Clause for finding items changed in the last 10 days

Features scheduled to be completed in the next 3 months

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

Not all fields are valid for all work item types (WITs). Jump to date fields for the set of fields you can include in queries and which WITs they apply to. Enter dates in the Date Pattern you set for your personal profile. (See Set personal preferences for details.)

Team's current iteration queries

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 which corresponds to the current iteration path for the team will be found. For example, if a team is on Sprint 5, then the query will return items assigned to Sprint 5. Later, when the team is working in Sprint 6, the same query will return 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 (aka 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

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

Prior to 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

Sliding window of team 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 paths (aka sprints) 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 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.

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 do not appear on the work item form, but they are tracked for those WITs listed in the following table.

Field name Description Work item type

Activated Date 1

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

All

Change Date

The date and time when a work item was modified.

Reference name=System.ChangedDate, Data type=DateTime

All

Closed Date 1

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 2

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

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

Task, Bug

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.

Reference name=System.IterationPath, Data type=TreePath

All

Resolved Date 1

The date and time when the work item was moved into a Resolved state.

Reference name=Microsoft.VSTS.Common.ResolvedDate, Data type=DateTime

Bug (Agile, CMMI)

Start Date 2

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

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

Task, Bug

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 should be completed.

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

Feature

Notes:

  1. 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 >  
    
  2. Start 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 do not appear on the work item form, but they are calculated for those 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 or Project. For more information, see Create your backlog and tasks using Project.

Client restrictions on the use of the @CurrentIteration macros

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

  • Web portal that connects to Azure Boards
  • Web portal that connects to an on-premises TFS 2015 or later version
  • Visual Studio 2015 or Team Explorer 2015 or later versions connected to Azure Boards or TFS 2015 or later versions.
  • 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 which 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.

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

SDK resources

To programmatically interact with queries, see Query for Bugs, Tasks, and Other Work Items.