How to display date difference in WIQL query?

Question

I am trying to display the time taken to close the user story via WIQL query, but getting an error as incorrect query, can anyone please let me know how to to do.

Query:

SELECT
    [System.Id],
    [System.WorkItemType],
    [System.Title],
    [System.AssignedTo],
    [System.State],
    [System.Tags],
    [Microsoft.VSTS.Common.ClosedDate]-[Microsoft.VSTS.Common.ActivatedDate] as TimeTaken
FROM workitems
WHERE
    [System.TeamProject] = @project
    AND [System.WorkItemType] <> ''
    AND [System.State] <> ''

Answer 1

WIQL query is not SQL query, we cannot set the WIQL like this :

[Microsoft.VSTS.Common.ClosedDate]-[Microsoft.VSTS.Common.ActivatedDate] as TimeTaken

Please see Work Item Query Language (WIQL) syntax reference for details.

However, we can call the WIQL REST API (Query By Wiql) in a PowerShell script, then call Get Work Items REST API to retrieve the ClosedDate and ActivatedDate of each work item in a loop. Then calculate the TimeTaken using New-TimeSpan utility.

Below PowerShell for your reference: (We can also export the query results to a *.csv file and open it with Microsoft Excel to track.)

Param(
   [string]$baseurl = "https://dev.azure.com/{organization}", 
   [string]$projectName = "ProjectName",
   [string]$user = "",
   [string]$token = "PAT"  
)

# Base64-encodes the Personal Access Token (PAT) appropriately
$base64AuthInfo = [Convert]::ToBase64String([Text.Encoding]::ASCII.GetBytes(("{0}:{1}" -f $user,$token)))
$filename = (Get-Date).ToString("yyyy-M-d") + "-" + "WI-DateDifference"

$uri = "$baseurl/$($projectName)/_apis/wit/wiql?api-version=5.1"

function CreateJsonBody
{
    $value = @"
{
  "query": "Select [System.Id], [System.Title], [System.State] From WorkItems Where [System.TeamProject] = @project AND [System.WorkItemType] <> '' AND [System.State] = 'Closed' order by [Microsoft.VSTS.Common.Priority] asc, [System.CreatedDate] desc"
}

"@
 return $value
}
$json = CreateJsonBody

#Get WI urls from the WIQL query.
$queryresult = Invoke-RestMethod -Uri $uri -Method POST -Body $json -ContentType "application/json" -Headers @{Authorization=("Basic {0}" -f $base64AuthInfo)}

$wiurls = $queryresult.workItems.url

$wis = @()

foreach($wiurl in $wiurls){

$wi = Invoke-RestMethod -Uri $wiurl -Method GET -Headers @{Authorization=("Basic {0}" -f $base64AuthInfo)}
$ActivatedDate = $wi.fields.'Microsoft.VSTS.Common.ActivatedDate'
$ClosedDate = $wi.fields.'Microsoft.VSTS.Common.ClosedDate'

if ($ActivatedDate){
    $TimeDiff = New-TimeSpan -Start $ActivatedDate -End $ClosedDate
    $Days = $TimeDiff.Days
    $Hrs = $TimeDiff.Hours
    $Mins = $TimeDiff.Minutes
    $Secs = $TimeDiff.Seconds
    $TotalSeconds =  $TimeDiff.TotalSeconds
    $TimeTaken = '{0:00}Days,{1:00}Hours,{2:00}Mins,{3:00}Secs' -f $Days,$Hrs,$Mins,$Secs

    $customObject = new-object PSObject -property @{
          "WitID" = $wi.id
          "WorkItemType" = $wi.fields.'System.WorkItemType'
          "Title" = $wi.fields.'System.Title'
          "AssignedTo" = $wi.fields.'System.AssignedTo'.displayName
          "State" = $wi.fields.'System.State'
          "Tags" = $wi.fields.'System.Tags'
          "TimeTaken" = $TimeTaken
          "TimeTakenTotalSeconds" =  $TimeDiff.TotalSeconds
          
        } 
    
    $wis += $customObject
 }      
}
    
$wis | Select-Object `
                WitID,
                WorkItemType,
                Title, 
                AssignedTo,
                State, 
                Tags,
                TimeTaken,
                TimeTakenTotalSeconds #| export-csv -Path D:\$filename.csv -NoTypeInformation -Append # Export to CSV, then open with EXCEL