How to include date criteria in RavenDB Lucene query

Question

I have following index in RavenHQ

from doc in docs 
let Tag = doc["@metadata"]["Raven-Entity-Name"]
where  Tag != null && Tag=="Email"
select new { Tag, LastModified = (DateTime)doc["@metadata"]["LastModified"], DateAdded=doc.DateAdded };

also I did make the DateAdded Indexing as Analyzed. I want to write a Lucene query which includes less than date criteria in the where clause.

I tried following, but it didn't work.

Where("DateAdded: [NULL TO 2012-12-31").ToList()

What is the exact way in C# to provide the date in where clause.

Thanks.

Matt Johnson-Pint Matt Johnson-Pint · Accepted Answer · 2013-03-16T16:01:42

The best way is to use Raven's API instead of constructing the query yourself:

var date = new DateTime(2012, 12, 31, 0, 0, 0, DateTimeKind.Utc);
session.Advanced.LuceneQuery<object>().WhereLessThan("LastModified", date);

If you must do it yourself, it's like this:

session.Advanced.LuceneQuery<object>()
                .Where("LastModified: {NULL TO 2012-12-31T00:00:00.0000000Z}")

Raven uses the Lucene Range Query Syntax paired with ISO8601 datetime format to 7 decimal places, as is provided by the Round Trip Format that is obtained from with dateTime.ToString("o").

Note that you said "less than" so I used the exclusive brackets { TO }. If you want "less than or equal" then you would use the inclusive brackets [ TO ] instead.

The query you provided in the question would have worked if you had included the ending ] character. However, it would not have been what you were looking for, since any LastModified entry with a time component would have fallen after the value you specified.

Also be aware that the LastModified metadata is stored as UTC.

How to include date criteria in RavenDB Lucene query

1 Answers