Friday, November 28, 2008

Wednesday, September 3, 2008

MOSS CAML Getting Month and Date Overlapping

Ever wanted to find overlapping date ranges?
For example if you have two date ranges and want to see if they overlap with existing date fields in MOSS.

Here's a picture of what I'm talking about:

imagePicture 1
Where..
MS = Parameter Month Start Date
ME = Parameter Month End Date
SD = MOSS Field Storing Start Date
ED = MOSS Field Storing End Date

The Picture 1 above produces the following logic:
 image Picture 2

There are three possible scenarios:

  • Overlapping dates on the start
  • Overlapping dates in the middle
  • Overlapping dates at the end

Here is the code to reproduce this:

The Code:



DateTime startDateToSearchOn; // parameter
DateTime endDateToSearchOn; // parameter
string startDateFieldName; // existing MOSS Field Internal name
string endDateFieldName; // existing MOSS Field Internal name
string startDateToSearchOnString = startDateToSearchOn.ToString("s");
string endDateToSearchOnString = endDateToSearchOn.ToString("s")
string startDateToSearchOnString = startDateToSearchOn.ToString("s");
string endDateToSearchOnString = endDateToSearchOn.ToString("s");
string queryXml = "<Query>"
+ fields.ToString()
+ "<Where>"
+ "<Or>"
+ "<Or>"
+ "<And>"
+ "<Leq>"
+ "<FieldRef Name='" + startDateFieldName + "' />"
+ "<Value Type='DateTime'>" + startDateToSearchOnString + "</Value>"
+ "</Leq>"
+ "<Geq>"
+ "<FieldRef Name='" + endDateFieldName + "' />"
+ "<Value Type='DateTime'>" + startDateToSearchOnString + "</Value>"
+ "</Geq>"
+ "</And>"
+ "<And>"
+ "<Geq>"
+ "<FieldRef Name='" + startDateFieldName + "' />"
+ "<Value Type='DateTime'>" + startDateToSearchOnString + "</Value>"
+ "</Geq>"
+ "<Leq>"
+ "<FieldRef Name='" + endDateFieldName + "' />"
+ "<Value Type='DateTime'>" + endDateToSearchOnString + "</Value>"
+ "</Leq>"
+ "</And>"
+ "</Or>"
+ "<And>"
+ "<Leq>"
+ "<FieldRef Name='" + startDateFieldName + "' />"
+ "<Value Type='DateTime'>" + endDateToSearchOnString + "</Value>"
+ "</Leq>"
+ "<Geq>"
+ "<FieldRef Name='" + endDateFieldName + "' />"
+ "<Value Type='DateTime'>" + endDateToSearchOnString + "</Value>"
+ "</Geq>"
+ "</And>"
+ "</Or>"
+ "</Where>"
+ "</Query>";



Friday, May 9, 2008

Tuesday, March 25, 2008

Monday, March 17, 2008

Tuesday, February 26, 2008

Wednesday, February 20, 2008