Query Lotus Domino calendar entries independent of date locale

Question

I have Java code which queries calendar entries from a Lotus Notes Domino server (based on a start and end date range). Below is a simplified version of the code.

When querying a Domino server that formats dates the same as the local client, everything is fine, e.g. both the server and client use m/d/y format. However, if the server and the client use different formats (e.g. a server with US formatting m/d/y and a client with German formatting d/m/y), then the wrong number of Lotus Notes entries are being found.

This is because I'm converting the dates into local strings using getLocalTime() and then creating a date range using @TextToTime().

Is there a way to find out what date format the server is using? Or is there a way to avoid the date-to-string conversion completely? I'd like to pass in the two Lotus DateTime objects and let the server decode them as needed.

import lotus.domino.*;


Session session = NotesFactory.createSession((String)null, (String)null, password);

Database db = session.getDatabase(dominoServer, mailfile, false);

// Get our start and end query dates in Lotus Notes format. We will query
// using the localized format for the dates.
lotus.domino.DateTime minStartDateLN = session.createDateTime(minStartDate);
lotus.domino.DateTime maxEndDateLN = session.createDateTime(maxEndDate);

// Query Lotus Notes to get calendar entries in our date range. 
// Here is an overview of this SELECT:
//   @IsAvailable(CalendarDateTime) is true if the LN document is a calendar entry
//   @Explode splits a string based on the delimiters ",; "
//   The operator *= is a permuted equal operator. It compares all entries on
//   the left side to all entries on the right side. If there is at least one
//   match, then true is returned. Explode is used because the CalendarDateTime
//   field can have many dates separated by ";" (e.g. for recurring meetings).
String calendarQuery = "SELECT (@IsAvailable(CalendarDateTime) & (@Explode(CalendarDateTime) *= @Explode(@TextToTime(\""
+ minStartDateLN.getLocalTime()
+ "-" + maxEndDateLN.getLocalTime() + "\"))))";

DocumentCollection queryResults = db.search(calendarQuery);

Answer 1

You could extract the individual year, month, day, hour, minute, and second values from your min and max using either the native Java or Lotus DateTime methods, and then build the dates in your query using @Date(year,month,day,hour,minute,second)

Answer 2

Try to format the date you are searching for - so it will be a common format in all locations: @TextToTime(@Text(CalendarDateTime))

Another idea is to use a tool like Ytria's ScanEZ to see all fields/data in the calendar entries. Believe there is a field that will have the dates saved in a common format that you can search against.

Answer 3

There is a way to find the date format on the Domino server by using the Java lotus.domino.International class (aka NotesInternational class). Here is the final working code.

import lotus.domino.*;

Session session = NotesFactory.createSession((String)null, (String)null, password);

Database db = session.getDatabase(dominoServer, mailfile, false);

String strDateFormat;
// Get the date separator used on the Domino server, e.g. / or -
String dateSep = session.getInternational().getDateSep();

// Determine if the server date format is DMY, YMD, or MDY
if (session.getInternational().isDateDMY()) {
    strDateFormat = "dd" + dateSep + "MM" + dateSep + "yyyy";                
}
else if (session.getInternational().isDateYMD()) {
    strDateFormat = "yyyy" + dateSep + "MM" + dateSep + "dd";
}
else {
    strDateFormat = "MM" + dateSep + "dd" + dateSep + "yyyy";
}

DateFormat dateFormat = new SimpleDateFormat(strDateFormat);


String calendarQuery = "SELECT (@IsAvailable(CalendarDateTime) & (@Explode(CalendarDateTime) *= @Explode(@TextToTime(\"" +
    dateFormat.format(startDate) + " - " + dateFormat.format(endDate) + "\"))))";

DocumentCollection queryResults = db.search(calendarQuery);