CustommetakeyCriteria

Question

We are using Filter Criteria's to retrieve values from Storage Database (SQL Server).

To retrieve various Event Component bases of their StartDate and EndDate we have written below mentioned code.

ItemSchemaCriteria isEvent= new ItemSchemaCriteria(SchemaID);
Query query= new Query();

Criteria mainCriteria;
mainCriteria = CriteriaFactory.And(isEvent, isEvent);

DateTime lowerDate = DateTime.Now;
DateTime lower;
string lowerDateString=lowerDate.ToString("yyyy-MM-dd HH:mm:ss.000");
lower=Convert.ToDateTime(lowerDateString);

CustomMetaKeyCriteria dateKeyCriteria = new CustomMetaKeyCriteria("EventStartDate",Criteria.GreaterThanOrEqual);
CustomMetaValueCriteria dateValueCriteria = new CustomMetaValueCriteria(dateKeyCriteria,lower);
CustomMetaKeyCriteria dateEndKeyCriteria = new CustomMetaKeyCriteria("EventEndDate",Criteria.GreaterThanOrEqual);
CustomMetaValueCriteria dateEndValueCriteria = new CustomMetaValueCriteria(dateEndKeyCriteria,lowerDate);
Criteria OrCriteria=CriteriaFactory.Or(dateValueCriteria,dateEndValueCriteria);
mainCriteria = CriteriaFactory.And(mainCriteria,OrCriteria);

When we specify exact date and time (2012-10-30 16:00:00.000) then above code gives us some records. But when we try to give Fromdate and ToDate (10 days span) then no records are returned, even though we have some records in Database. Is there any mistake we are making in specifying dateKeyCriteria which will return us all events between specified EventStartDate and EventEndDate?

Answer 1

You should know that your logic in constructing the query is not really correct but anyways you have another option (much more optimal) that you can use: CustomMetaDateRangeCriteria. You have a few constructors that allow you to specify the beginRange, endRange, format (if your dates are not in DateTime format), include beginDate/endDate. Here is an example:

CustomMetaDateRangeCriteria dateRangeCriteria = new CustomMetaKeyCriteria("MyMetaDate", myStartDate, myEndDate, true);

If this constructor is not suited for you you can also try another constructor: CustomMetaDateRangeCriteria(string fieldName, string dateformat, string beginRange, string endRange, bool rangeIncluded).

Hope this helps.

Answer 2

In case it can help, here you have a fully working example of how to use CustomMetaDateRangeCriteria, note that cbo is a list where the user can select a year with format "YYYY". That would create a filter retrieving items for a whole year.

...

CustomMetaDateRangeCriteria yearCriteria = new CustomMetaDateRangeCriteria("publicationDate", GetFirstDateTimeInYear(cboYear.SelectedValue), GetLastDateTimeInYear(cboYear.SelectedValue));

...

    private DateTime GetFirstDateTimeInYear(string year)
    {
        return new DateTime(int.Parse(year), 1, 1);
    }

    private DateTime GetLastDateTimeInYear(string year)
    {
        return new DateTime(int.Parse(year) + 1, 1, 1).AddDays(-1);
    }