Friday, January 18, 2008

Specifying date and time in WIQL queries

When creating Work Item queries, sometimes it may be required to specify not only date, but time also for the query condition. For myself, I usually use that when I need to find all work items changed relative to certain event - for example, labeling of the source code; and as it happens, there may be several such events in one day, so the time is of importance.

The obvious solution is to specify a query similar to "Closed Date > 1/1/2008 10:00" (or in pure WIQL: [Microsoft.VSTS.Common.ClosedDate] > '2008-01-01T10:00:00.0000000').

But life is not that simple. What you get on running the query is the error message, saying "You cannot supply a time with the date when running a query using date precision. The error is caused by '[Microsoft.VSTS.Common.ClosedDate] > '2008-01-01T10:00:00.000'".

Without trying to analyze the message too much, one can surmise from it that currently only dates can be specified in query conditions, and apparently you need to change the precision from date to "date time". Easy enough, huh? So how do we change the precision?

You are in for a surprise - you cannot change the precision. If you ran the query in VS, you will always have the date precision and will not be able to specify times. However, there is sort of workaround if you are really hot for the query results. You will have to write a small bit of code (interestingly, the object model API allows one to specify the precision for the query), as demonstrated below:


TeamFoundationServer server = TeamFoundationServerFactory.GetServer("10.18.40.201");

WorkItemStore wit = new WorkItemStore(server);

stringwiql = @"SELECT [System.Id], [Microsoft.VSTS.Common.ClosedDate] 

                FROM WorkItems 

                WHERE [System.TeamProject] = 'Project1' AND 

                [Microsoft.VSTS.Common.ClosedDate] > '2008-01-01T10:00:00.00' 

                ORDER BY [System.Id]";

Query qry = new Query(wit, wiql, null, false);

ICancelableAsyncResult car = qry.BeginQuery();

WorkItemCollection items = qry.EndQuery(car);


The trick is in the fourth parameter passed to the Query class constructor (setting false specifies that date and time precision should be used instead of default date precision).

The documentation on that parameter is rather cryptic; I am not aware of any additioanl docs on WIQL precision.

While the issue is not critical, the lack of the date time precision in VS work item queries is a bit of a nuisance (as far as I know, VS2008 still suffers from that). Hope that Rosraio will handle that.

4 comments:

B. Huard said...

I did not known it was also a problem in WIQL.

I had a problem of being able to enter the time in a date field (it is a date time field after all).

I entered my recommendation on MS connect and it was shot down.
https://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=249688

I got a feeling it is not high in the list at MS.

eugenez said...

I guess, most people are happy with the date only.

The suggestion you have entered has workaround - you can create a custom control wih time picker; probably that's why it is closed.

Anonymous said...

I just ran into this exact issue - thanks for the pointer, I'd not have 'guessed' this.

Gluegood Software said...

Hi,

Thats for this great post.

I've used it to create a TFS LogParser COM input plugin (freeware).

http://gluegood.blogspot.com/2009/08/freeware-logparsertfsquery-team.html

Thanks once again