Skip to main content

Query list with CAML - DateTime column

Scenario
We have a SharePoint list with the column DateOfBirth which is DateTime column. We need to filter or query this list programmatically to get items where date of birth is in the certain range

Solution

SPList list = ... // initialize your list
SPQuery query = new SPQuery();
DateTime dtDateFrom = ..., dtDateTo = ...; // our DateTime values

query.Query = string.Format("<Where><And><Leq><FieldRef Name='DateOfBirth' /><Value Type='DateTime'>{0}</Value></Leq><Geq><FieldRef Name='DateOfBirth' /><Value Type='DateTime'>{1}</Value></Geq></And></Where>", SPUtility.CreateISO8601DateTimeFromSystemDateTime(dtDateTo), SPUtility.CreateISO8601DateTimeFromSystemDateTime(dtDateFrom));

SPListItemCollection items;

items = list.GetItems(query);

The above query will use only date value without time. If you would like to take time into account as well it has to look like the following:

query.Query = string.Format("<Where><And><Leq><FieldRef Name='DateOfBirth' /><Value Type='DateTime' IncludeTimeValue='True'>{0}</Value></Leq><Geq><FieldRef Name='DateOfBirth' /><Value Type='DateTime' IncludeTimeValue='True'>{1}</Value></Geq></And></Where>", SPUtility.CreateISO8601DateTimeFromSystemDateTime(dtDateTo), SPUtility.CreateISO8601DateTimeFromSystemDateTime(dtDateFrom));


Comments

  1. Hello ,

    Could you please help me for applying format in ViewField tag of SPQuery ?
    Please find my code as below :


    Here i want the 'Training_x0020_Date' in date format only at the time of fetching rows from List.

    Thank You

    ReplyDelete

Post a Comment