CAML and DateTime issues and the solution.

Hi Friends,

During one of my projects, I had to work on SPQuery objects, SPList, SPListItemCollection, CAML and date time formats. Let me introduce me business case:

Business case:
Its for the team calendar. If a team member apply for vacations and public holiday falls during his/her vacations then system should deduct that number of public holidays.

Roadblocks:
1) there is no straight forward method for filtering date time functions.
2) SharePoint includes date and time parts. But my case just needs date and not time.

My Solution:
I created a function called as GetBankHolidays with 3 parameters:
1) Start Date for team member (date1)
2) End date for team member (i.e. End date - Start date = total number of days for his vacation) (date2)
3) SPItemEventProperties


4) Firstly, get date range for SPQuery.
It can easily defined like this:

Start date of team member
End date of team member

>> query.Query = "<Where><And><Geq><FieldRef Name='Day' /><Value Type='DateTime' IncludeTimeValue='FALSE'>" + date1 + " </Value></Geq><Leq><FieldRef Name='Day' /><Value Type='DateTime' IncludeTimeValue='FALSE'>" + date2 + "</Value></Leq></And></Where>";


5) Secondly, we need to use SharePoint function. But we need to include one more namespace: using Microsoft.SharePoint.Utilities;
SPUtility.CreateISO8601DateTimeFromSystemDateTime(date1) and
SPUtility.CreateISO8601DateTimeFromSystemDateTime(date2).

>> query.Query = String.Format("<Where><And><Geq><FieldRef Name='Day' /><Value Type='DateTime' IncludeTimeValue='FALSE'>{0}</Value></Geq><Leq><FieldRef Name='Day' /><Value Type='DateTime' IncludeTimeValue='FALSE'>{1}</Value></Leq></And></Where>", SPUtility.CreateISO8601DateTimeFromSystemDateTime(date1), SPUtility.CreateISO8601DateTimeFromSystemDateTime(date2));

6) Thirdly, we also need to ignore the time part. This can be done by "IncludeTimeValue" attribute.

>> query.Query = String.Format("<Where><And><Geq><FieldRef Name='Day' /><Value Type='DateTime' IncludeTimeValue='FALSE'>{0}</Value></Geq><Leq><FieldRef Name='Day' /><Value Type='DateTime' IncludeTimeValue='FALSE'>{1}</Value></Leq></And></Where>", SPUtility.CreateISO8601DateTimeFromSystemDateTime(date1), SPUtility.CreateISO8601DateTimeFromSystemDateTime(date2));

The final function be obtained by GetBankHolidays.

---

/// <summary>

/// Computes no. of bank holidays during user's vacation and return an INT ( = number of bank holidays)

/// </summary>

/// <param name="date1"></param>

/// <param name="date2"></param>

/// <returns></returns>

public static int GetBankHolidays(DateTime date1, DateTime date2, SPItemEventProperties properties)

{
using (SPWeb web = properties.OpenWeb())
{
// counter for bank holidays

int count = 0;


SPListItemCollection items = null;
SPList holidaysList = web.Lists["Bank Holidays"];



SPQuery query = new SPQuery();



query.Query = String.Format("<Where><And><Geq><FieldRef Name='Day' /><Value Type='DateTime' IncludeTimeValue='FALSE'>{0}</Value></Geq><Leq><FieldRef Name='Day' /><Value Type='DateTime' IncludeTimeValue='FALSE'>{1}</Value></Leq></And></Where>", SPUtility.CreateISO8601DateTimeFromSystemDateTime(date1), SPUtility.CreateISO8601DateTimeFromSystemDateTime(date2));




while (date1.Date <= date2.Date)

{
items = holidaysList.GetItems(query);



foreach (SPListItem item in items)
{
if (Convert.ToDateTime(date1.Date) == Convert.ToDateTime(item["Day"]))
count++;
}
date1 = date1.AddDays(1);

}

return count;

}



Happy programming!

Cheers,
Aroh