Iguana Gears

SharePoint Web.Config Changes for Dev Machine

2009-07-23T07:56:00.001-07:00

This post has been moved here: Programmer Computer

Watin working with window.showModalDialog()

2009-01-22T00:47:00.001-08:00

This post has been moved here: Programmer Computer

WCF web.config file for SharePoint Development Environment

2008-12-03T04:57:00.001-08:00

This post has moved here: Programmer Computer

SharePoint Custom RSS Feed Aggregator

2008-11-28T13:12:00.001-08:00

This post has moved here: Programmer Computer

Dev Environment: Setting up Visual Studio 2008 SP 1 for Silverlight 2 RTW 2.0.31005.0

2008-11-20T00:54:00.001-08:00

This post has moved here: Programmer Computer

MOSS CAML Getting Month and Date Overlapping

2008-09-03T10:40:00.001-07:00

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:

Picture 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:
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>";