CAML Query using Date-Time Columns

Question

Hi

I have a document library with two columns say Col1 and Col2 both of type 'DateTime'. Now I want to write a CAML query to get all those items for which the value of Col1 is greater than value of Col2.

Thanks,

Answer 1

i don;t think CAML can do a comparison between field, since it is cannot do any join.

try to use this alternative http://social.technet.microsoft.com/Forums/en-US/sharepointdevelopment/thread/c336abda-950b-41dc-9982-0aab52ee7372/

or try to instead use a Linq 

var 

CollectionQry = from SPListItem item  
in list.Items
where (DateTime)item["Created"] < (DateTime)item["Modified"]
select new
{
Guid = item.ID,
Name = item.Title
};

Answer 2

Howdy,

We cant use CAML query to compare two fields value in same list item. But you can filter the items by using DataTable and DataView. I have specified the tested code below for your requirement.

public DataTable FilterByDate()
{                        
    SPSite site = new SPSite(_WebUrl);
    using (site)
    {
        SPWeb web = site.OpenWeb();

        using (web)
        {
            SPList list = web.Lists[_ListName];
            DataTable listData = list.Items.GetDataTable();

            if (listData != null)
            {
                DataView listDV = new DataView(listData);
                listDV.RowFilter = "DateCol1 > DateCol2";
                return listDV.ToTable();
            }

        }
    }            
}

In case of any clarification, please let me know.

Good Luck.

---

Gopinath D - http://sharepointsnippets.com

Answer 3

You can look at the DateRangeOverlap element in CAML to see if this will work for you.

http://msdn.microsoft.com/en-us/library/ms436080.aspx

<Query>
   <Where>
      <DateRangesOverlap>
         <FieldRef Name="EventDate"></FieldRef>
         <FieldRef Name="EndDate"></FieldRef>
         <FieldRef Name="RecurrenceID"></FieldRef>
         <Value Type="DateTime">
            <Now/>
         </Value>
      </DateRangesOverlap>
   </Where>
</Query>

---

http://www.certdev.com