Plugin Developer Discussion

Discussion for FogBugz Plugin developers

Querying with dates when FogBugz database is MS Access

I'm working on a plugin that will report development activities for a user-selected date range.  The database on my development box is MS Access.  I cannot seem to get the WHERE clause to work properly.  I've tried the following:

CSelectQuery query = api.Database.NewSelectQuery("Bug");
query.AddInnerJoin("TimeInterval", "Bug.ixBug = TimeInterval.ixBug");
query.AddWhere("TimeInterval.dtStart <= '" + strStartDate + "'");
query.AddWhere("TimeInterval.dtEnd <= '" + strEndDate + "'");

I've also tried surrounding the date with #, like this:
query.AddWhere("TimeInterval.dtEnd <= #" + strEndDate + "#");

Neither of these work - they both produce exceptions. If I do this:
query.AddWhere("TimeInterval.dtStart >= " + strStartDate);
query.AddWhere("TimeInterval.dtEnd <= " + strEndDate);

there is no exception, but the results are incorrect - it returns an empty set when I know for sure I have cases that fall in this interval.  What is the correct way to programatically select from a date range like this?  I've not been able to find an example that shows how to do this.

Shawn Bradley Send private email
Tuesday, September 15, 2009
Hi Shawn,

The correct way to do this is to use a query parameter:

query.AddWhere("TimeInterval.dtStart >= @dtStart");
query.AddWhere("TimeInterval.dtEnd <= @dtEnd");
query.SetParamDate("dtStart", dtStart);
query.SetParamDate("dtEnd", dtEnd);
David Fullerton Send private email
Tuesday, September 15, 2009
Thanks!  That worked marvelously.
Shawn Bradley Send private email
Tuesday, September 15, 2009

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz