Saturday, January 19, 2008

Using WIQL EVER with dates - dont!

One of very neat predicates available in Work Item Query Language is EVER conditional clause. It allows one to specify conditions similar to "was the value of the <field> ever <X>". Typical example of a query - list of all tasks ever assigned to myself:

SELECT [System.Id], [System.AssignedTo]
FROM WorkItems
WHERE [System.TeamProject] = 'Project1' AND [System.AssignedTo] EVER @me
ORDER BY [System.Id]

That feature is very convinient (saves all that ugly EXISTS queries of traditional SQL); however, it turned out it is not without its limitation.

Some time ago there was an interesting question on MSDN forums: "How one would use EVER to get work items specified at certain date?" Though MSDN says that date time fields should work with EVER operator, in reality it appears that the operator will not function correctly.

My guess is that EVER uses the exact field value to test against - and for the date time fields that includes time portion of the value. That of course limits its usefulness (while the query of whether certain work item was modified on date X is of value, nobody cares what work item was modified on date X at 12:33.56 :).

So the purpose of that post is to save you some time - do not attempt to use EVER with dates; depending on your actual objective the answer may be in WIQL or you might have to do additional filtering programmatically.

No comments: