SharePoint Add-In - CAML Query to check if a specific date is part of calendar event

Question

I have a list with leave requests which a start and end date. I want to check for a leave request if the dates between starting date and ending date of the leave request . already exist in a calendar. I do that with CAML Query.

Here is my caml :

  CamlQuery getHolidaysQuery = new CamlQuery();



                getHolidaysQuery.ViewXml =
                    @"<View>
                            <Query>
                                <Where>
                                    <And>
                                    <Geq>
                                        <FieldRef Name='EventDate' />
                                        <Value IncludeTimeValue='FALSE' Type = 'DateTime'>" + currentDate. + @" </Value>
                                    </Geq>
                                    <Leq>
                                        <FieldRef Name='EndDate' />
                                        <Value IncludeTimeValue='FALSE'  Type = 'DateTime'>" + currentDate + @" </Value>
                                    </Leg>
                                    </And>
                                </Where>
                            </Query>
                           </View>";

                ListItemCollection getHolidaysItems = holidaysCalendar.GetItems(getHolidaysQuery);

                clientContext.Load(getHolidaysItems);
                clientContext.ExecuteQuery();

It always returns Count = 0 which means it didn't find any items on the calendar. I 've created before an item in calendar so that the CAML Query should return something more than 0.

If anyone could share his wisdom on this i would be grateful.

Thanks

Answer 1

Although Sharepoint stores dates column as UTC but when you are comparing this using CAML query, there is a little tweak in this because of Time zone. you have to set StorageTZ attribute to TRUE to compare dates using the native storage time zone (UTC time).

Make sure you convert your variable "currentDate" to UTC. Ref

Your UTC converted date should look like :2014-04-14T21:30:46Z. Refer this code:

<Query>
     <Where>
         <And>
            <Geq>
               <FieldRef Name='EventDate' />
               <IncludeTimeValue='TRUE' StorageTZ='TRUE' Type = 'DateTime'>" + currentDate. + @" </Value>
            </Geq>
            <Leq>
               <FieldRef Name='EndDate' />
               <Value IncludeTimeValue='TRUE' StorageTZ='TRUE'  Type = 'DateTime'>" + currentDate + @" </Value>
            </Leg>
         </And>
    </Where>