Quick Fix: Indexing DateTime Fields in Sitecore 7

There is a known limitation in Sitecore 7's handling of DateTime fields, whereby, only the year, month, and day (i.e. yyyyMMdd) will get indexed. The time portion of your field will be ignored.

Say, for example, you are developing a time-sensitive application within Sitecore, such as a calendar. Each of your calendar event items have a StartDateTime and EndDateTime field. You've been asked to display a list of upcoming events. Events that are underway or have passed should fall off the list. To achieve this, one might begin with the following query:

using (var context = ContentSearchManager.GetIndex("sitecore_web_index").CreateSearchContext())
{
    var results = context.GetQueryable<CalendarEventContentSearchItem>()
        .Where(i => i.StartDateTime > DateTime.Now);

    // NOTE: Assume current date/time is 1/1/2015 4:00PM
}

However, if you inspect the search logs for the above query, you'll find that it resulted in the following Lucene query: +startdatetime:[20150101 TO *]. Ummmm... ok, so it looks like we lost the time portion of our query, and as a result, we'll only get events for 1/2/2015 and onward.

Sadly, this limitation still exists in the latest versions of Sitecore 7, including 7.2 and 7.5. Uli Weltersbach outlined a fix for Sitecore 7.0 on his blog, however, for Sitecore 7.1+, some additional adjustments are necessary.

Specifically, the ContentSearch configuration has undergone minor schema changes between versions. Additionally, because of the way Lucene handles range-based queries in lexicographical order, you should use yyyyMMddHHmmss (the equivalanet of Lucene.NET's SECOND date/time resolution) as your default date format to ensure that you queries are returning exactly what you expect. Using the "s" DateTime format will produce an ISO 8601 format that is indeed sortable, however, from my experience, you may get mixed results with this format for date-based queries.

<configuration xmlns:patch="http://www.sitecore.net/xmlconfig/">
  <sitecore>
    <settings>
      <setting name="ContentSearch.DateFormat">
        <patch:attribute name="value">yyyyMMddHHmmss</patch:attribute>
      </setting>
      <setting name="ContentSearch.AlternateDateTimeFormats" value="yyyyMMdd" />
    </settings>
    <contentSearch>
      <indexConfigurations>
        <defaultLuceneIndexConfiguration type="Sitecore.ContentSearch.LuceneProvider.LuceneIndexConfiguration, Sitecore.ContentSearch.LuceneProvider">
          <fieldReaders type="Sitecore.ContentSearch.FieldReaders.FieldReaderMap, Sitecore.ContentSearch">
            <mapFieldByTypeName hint="raw:AddFieldReaderByFieldTypeName">
              <fieldReader fieldTypeName="date|datetime">
                <patch:attribute name="fieldReaderType">Hedgehog.IndexDateTimeFix.DateTimeFieldReader, Hedgehog.IndexDateTimeFix</patch:attribute>
              </fieldReader>
            </mapFieldByTypeName>
          </fieldReaders>
          <indexFieldStorageValueFormatter type="Sitecore.ContentSearch.LuceneProvider.Converters.LuceneIndexFieldStorageValueFormatter, Sitecore.ContentSearch.LuceneProvider">
            <converters hint="raw:AddConverter">
              <converter handlesType="System.DateTime">
                <patch:attribute name="typeConverter">Hedgehog.IndexDateTimeFix.DateTimeConverter, Hedgehog.IndexDateTimeFix</patch:attribute>
              </converter>
            </converters>
          </indexFieldStorageValueFormatter>
        </defaultLuceneIndexConfiguration>
      </indexConfigurations>
    </contentSearch>
  </sitecore>
</configuration>