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));


Labels:

Comments

  1. AnonymousOctober 9, 2011 at 2:12 AM

    Thanks

    ReplyDelete
  2. UnknownApril 6, 2016 at 1:33 AM

    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

Popular posts from this blog

Setting up External Content Type for SQL Server database using SQL Server authentication - SharePoint 2010 Foundation

This post is a follow up on the issues that I have got setting up External Content Type (ECT) on SharePoint 2010 Foundation that was going to connect to remote SQL Server database for information. I cannot use my SharePoint user accounts to access SQL Server. According to the information I have discovered ECT and Business Connectivity Services are available in the SharePoint 2010 Foundation, but there are some issues if you want to use authentication methods in your external connections that are different from Windows Identity or Current User Identity. This is because there is no Secure Store Service in SharePoint 2010 Foundation which serves as an impersonation hub and is only available in SharePoint 2010 Server edition. The issues are coming from the fact that you can actually create ECT in SharePoint Designer 2010 providing just Secure Store ID and system would ask you for credentials and here you go, but when you try to use your ECT in External Lists or as a lookup columns you w...
37 comments
Read more

SharePoint 2013 Development and Consulting - Laptop & Conferencing Experience with Lync

Have just jumped on a brand new SharePoint 2013 massive Intranet project and because of specific working conditions have found some items that required addressing almost immediately in order to continue the job. Maybe this will be interesting to someone else who is about to start SharePoint 2013 development to have an idea of what might be necessary. I started working with SharePoint using my own infrastructure in 2009. I used iMac with 320GB HDD and 4GB RAM running VMWare Fusion to virtualise Windows  environment ( SQL Server 2008, standalone SharePoint 2007, Visual Studio 2008, Office 2007). It was all-in-one virtual machine. A bit slow, but enough for any SharePoint work that I had at that time. For communication with colleagues and partners we used GoToMeeting . Great tool that worked (and still works) without any problems. Voice, video, screen-sharing - all worked well using built-in audio/video hardware. In 2010 I got a MacBook Pro  with 500GB HDD and 8GB ...
Post a Comment
Read more

InfoPath 2013 Preview - URL not valid error when publishing

In InfoPath 2010 there is a problem when you try to publish a Form to a SharePoint site that doesn't have root site collection. You will get an error message "The following url is not valid". It's described here:   http://support.microsoft.com/kb/981854 The solution is to create root site collection for the Web application. Same problem is with InfoPath 2013: In my situation I didn't create the root site collection initially, but used "/sites" managed path instead for all site collections. To solve the problem with InfoPath I had to create it. Used a new site template "Project Site". Will get a chance to research that too. :)  
Post a Comment
Read more