0

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

0

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>

  • Thanks for the response. Actually i don't need the time . So i tried to IncludeTimeValue= False and still it returned 0 results – shak Jun 15 '17 at 13:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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