3

I'm trying to select records by date from a Lotus Notes database and have run into trouble with correctly formatting the date.

Here's the relevant code:

public void runNotes()  {
    Session s;
    try {
        s = NotesFactory.createSession((String)null, (String)null, "mypassword");
        Database hkDB = 
            s.getDatabase("NBHDH001/YNM", "H\\DHH00001.nsf", false);
        DocumentCollection docs = hkDB.search("[Date]>[2012/03/20]");

Date is a field in the record, and when I looked up records (with FTSearch), the date came back in the format above: [yyyy/mm/dd].

The parameter of the search is what I need here. i.e. what should I put instead of "[Date]>[2012/03/20]"

I tried various constructions with Calendar and DateFormat, but it's not coming together...

Any suggestions?

grooble
  • 617
  • 1
  • 8
  • 27

2 Answers2

5

You should get rid of the square brackets on the field name. The search method expects a Notes Formula, like what you'd put into a view selection formula:

"Date > [03/20/2012]"

It might also be required that dates are in mm/dd/yyyy format, though if you are in a non-US locale I'm not 100% sure.

Ken Pespisa
  • 21,989
  • 3
  • 55
  • 63
  • It worked. After everything I tried... square brackets. Keep it simple, I suppose... Thanks a lot. – grooble Jun 01 '12 at 01:35
  • Woops, I just checked what was in the returned DocumentCollection and it's null. I happen to know that there are lots of records that meet this date criteria. Could it be because "Date" is not a text field but type 1024: date-time or range of date-times? – grooble Jun 01 '12 at 08:02
  • 1
    On second look of this, I think I had it wrong - the brackets are appropriate for the date constant, just not the Date field variable. I've updated the answer. I believe my original suggestion compared Date to the calculation of 3 divided by 20 divided by 2012 :) – Ken Pespisa Jun 01 '12 at 13:05
  • 2
    @grooble, I would try working out the kinks of the search formula by creating a view and experimenting with the formula right in the view designer. You can get instant feedback that way. – Ken Pespisa Jun 01 '12 at 13:10
3

You mentioned that you have been doing full text searches in the database, so it is definitely worth mentioning this... If the database actually has a full text index, then you may want to consider using the NotesDatabase.FTSearch() method instead of NotesDatabase.Search(). The FTSearch method will be considerably faster for a large database.

The syntax for FTSearch is different from the syntax for Search. You could use either "FIELD Date > 03/20/2012" or "[Date] > 03/20/2012".

Richard Schwartz
  • 14,463
  • 2
  • 23
  • 41
  • 1
    See problems above. And, yep, the line you provided above worked with FTSearch and gave me records. Thanks to both of you! You're on my list of vote-ups for when I hit 15 rep. ;) – grooble Jun 01 '12 at 08:09