1

I need to check an SPList if it contains Activities that have overlapping DateTime values.

An Activity is a type which contains:

  • Date on which the activity was done (ActivityDate)
  • Start time of the activity (ActivityStartTime)
  • End time of the activity (ActivityEndTime)

I have already applied the accepted answer from this SO post but I need to do this in CAML so it's a little more efficient.

Here is my method which does the whole thing:

    public static bool HandleItemAdded(SPListItem item, SPList list)
    {
        bool conflictExists = false;

        SPListItem currentItem = item;

        //Get data of new item...
        DateTime currentItemDate = DateTime.Parse(currentItem[Variables.activityDateGuid].ToString());
        DateTime tempStart = DateTime.Parse(currentItem[Variables.activityStartTimeGuid].ToString());
        DateTime tempEnd = DateTime.Parse(currentItem[Variables.activityEndTimeGuid].ToString());
        DateTime currentItemStartTime = new DateTime(currentItemDate.Year, currentItemDate.Month, currentItemDate.Day, tempStart.Hour, tempStart.Minute, tempStart.Second);
        DateTime currentItemendTime = new DateTime(currentItemDate.Year, currentItemDate.Month, currentItemDate.Day, tempEnd.Hour, tempEnd.Minute, tempEnd.Second);

        //Get all items who have conflicts...
        SPQuery query = new SPQuery()
        {
            Query = "<Where>" +
                            "<And>" +
                                "<And>" +
                                    "<And>" +
                                        "<Eq>" +
                                            "<FieldRef Name=\"ActivityDate\" />" +
                                            "<Value IncludeTimeValue=\"FALSE\" Type=\"DateTime\">" + SPUtility.CreateISO8601DateTimeFromSystemDateTime(currentItemDate) + "</Value>" +
                                        "</Eq>" +
                                        "<Gt>" +
                                            "<FieldRef Name=\"ActivityEndTime\" />" +
                                            "<Value Type=\"DateTime\">" + SPUtility.CreateISO8601DateTimeFromSystemDateTime(currentItemStartTime) + "</Value>" +
                                        "</Gt>" +
                                    "</And>" +
                                    "<Neq>" +
                                        "<FieldRef Name=\"ID\" />" +
                                        "<Value Type=\"Number\">" + currentItem.ID + "</Value>" +
                                    "</Neq>" +                                        
                                "</And>" +
                                "<Lt>" +
                                    "<FieldRef Name=\"ActivityStartTime\" />" +
                                    "<Value Type=\"DateTime\">" + SPUtility.CreateISO8601DateTimeFromSystemDateTime(currentItemendTime) + "</Value>" +
                                "</Lt>" +
                            "</And>" +
                        "</Where>" 
        };

        SPListItemCollection items = list.GetItems(query); //Always empty here...

        // Do something with items...

    }

My problem is, I get no returns even if there should be. The ID and Date filters are working as they return the correct values if I take out the other two. But vice versa I also get nothing back.

Does anybody know what I'm doing wrong here?

EDIT:

Weird. When I substitute the <Gt> and <Lt> with <Geq> and <Leq>, respectively, it works but I also get conflicts when I create two activities like this:

  1. A1: 10.02.2015 09:00 - 10:00
  2. A2: 10.02.2015 10:00 - 11:00

This should not cause a conflict.

4
  • I'd try getting the BIWUG CAML Designer and try crafting your query for verification, biwug.be/resources Jul 31, 2015 at 17:41
  • I already did inspecting with SP CAML Query Helper from MS website, but I have not yet found the right constellation of filters. Jul 31, 2015 at 17:49
  • Have you check if it can be a timezone problem ? My dates are UTC when I use CAML builders.
    – JayHell
    Jul 31, 2015 at 18:25
  • nope. as I said, when I only filter through ActivityDate and ID, it works. only when I also filter through ActivityStartTime and ActivityEndTime I get no results. Jul 31, 2015 at 18:26

1 Answer 1

1

Ok I got the solution. Apparently, the default format of a DateTime field when returned through CAML only contains the date part. When I do it like this, I get the expected results:

            Query = "<Where>" +
                        "<And>" +
                            "<And>" +
                                "<And>" +
                                    "<Eq>" +
                                        "<FieldRef Name=\"ActivityDate\" />" +
                                        "<Value IncludeTimeValue=\"FALSE\" Type=\"DateTime\">" + SPUtility.CreateISO8601DateTimeFromSystemDateTime(currentItemDate) + "</Value>" +
                                    "</Eq>" +
                                    "<Gt>" +
                                        "<FieldRef Name=\"ActivityEndTime\"  IncludeTimeValue=\"TRUE\" />" + //Give me also the time...
                                        "<Value Type=\"DateTime\">" + SPUtility.CreateISO8601DateTimeFromSystemDateTime(currentItemStartTime) + "</Value>" +
                                    "</Gt>" +
                                "</And>" +
                                "<Neq>" +
                                    "<FieldRef Name=\"ID\" />" +
                                    "<Value Type=\"Number\">" + currentItem.ID + "</Value>" +
                                "</Neq>" +                                        
                            "</And>" +
                            "<Lt>" +
                                "<FieldRef Name=\"ActivityStartTime\"  IncludeTimeValue=\"TRUE\" />" + //Give me also the time...
                                "<Value Type=\"DateTime\">" + SPUtility.CreateISO8601DateTimeFromSystemDateTime(currentItemendTime) + "</Value>" +
                            "</Lt>" +
                        "</And>" +
                    "</Where>" 
1
  • I think you can skip the first ActivityDate validation, since the StartTime/EndTime also includes the date.
    – JayHell
    Jul 31, 2015 at 18:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.