locked
CAML Query using Date-Time Columns RRS feed

  • 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,
    Tuesday, March 31, 2009 5:11 AM

Answers

  • 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
    };
    Tuesday, March 31, 2009 7:56 AM
  • 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
    Tuesday, March 31, 2009 8:03 AM

All replies

  • 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
    };
    Tuesday, March 31, 2009 7:56 AM
  • 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
    Tuesday, March 31, 2009 8:03 AM
  • 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
    Tuesday, March 31, 2009 4:09 PM