none
CAML Query - DateTime

    Question

  • Hello!
    I wish to take data from the list, satisfying to the certain time range (BeginDate.....DateTime.....EndDate). How it can be made?
    Monday, March 24, 2008 2:27 PM

Answers

  •  

    This CAML Query below return the items based on MyDateColumn with less than 5 days. Basically using a Greator or Equal operatior and providing Today date - 5 days as a filter.
     
    <Query>
        <Where>
            <Geq>
                <FieldRef Name="MyDateColumn" />
                <Value Type="DateTime">
                    <Today OffsetDays="-5" />
                </Value>
            </Geq>
        </Where>
    </Query>
     
     
    You can use greator or equal like above and less or equal filter with AND operator in CAML above to work with date range. 
     
    If the date value is coming from your variable etc then you need to use SPUtility function and get the string required by CAML for date time variable.
     
       DateTime CustomDate = DateTime.Now;
                string strCustomDate  = SPUtility.CreateISO8601DateTimeFromSystemDateTime(CustomDate); 
     
     
    Monday, March 24, 2008 3:42 PM

All replies

  •  

    This CAML Query below return the items based on MyDateColumn with less than 5 days. Basically using a Greator or Equal operatior and providing Today date - 5 days as a filter.
     
    <Query>
        <Where>
            <Geq>
                <FieldRef Name="MyDateColumn" />
                <Value Type="DateTime">
                    <Today OffsetDays="-5" />
                </Value>
            </Geq>
        </Where>
    </Query>
     
     
    You can use greator or equal like above and less or equal filter with AND operator in CAML above to work with date range. 
     
    If the date value is coming from your variable etc then you need to use SPUtility function and get the string required by CAML for date time variable.
     
       DateTime CustomDate = DateTime.Now;
                string strCustomDate  = SPUtility.CreateISO8601DateTimeFromSystemDateTime(CustomDate); 
     
     
    Monday, March 24, 2008 3:42 PM
  • Hi,
    I have a question.
    Can caml works with hours and minutes too ?
    Because my doesn't :-(

    I built this query


    DateTime start = Convert.ToDateTime(_properties.AfterProperties["start"].ToString()); 
    DateTime end = Convert.ToDateTime(_properties.AfterProperties["end"].ToString()); 
     
     
                    SPQuery query1 = new SPQuery(); 
                    query1.Query = "<Where>"
                                       "<And>" + 
                                           "<Eq>" + //Equal to 
                                             "<FieldRef Name='reason'></FieldRef>" + 
                                             "<Value Type='Text'>" + whatIsbooking + "</Value>" + 
                                            "</Eq>" + 
                                         "<Or>" + 
                                                "<And>" +  
                                                "<Geq>" + // Greater than or euqual to 
                                                    "<FieldRef Name='end'></FieldRef>" + 
                                                    "<Value Type='DateTime'>" + SPUtility.CreateISO8601DateTimeFromSystemDateTime(end) + "</Value>" + 
                                                "</Geq>" + 
                                                "<Leq>" + //less then or equal to 
                                                    "<FieldRef Name='start'></FieldRef>" + 
                                                    "<Value Type='DateTime'>" + SPUtility.CreateISO8601DateTimeFromSystemDateTime(end) + "</Value>" + 
                                                "</Leq>" + 
                                              "</And>" + 
                                              "<And>" +  
                                                "<Leq>" +  
                                                    "<FieldRef Name='start'></FieldRef>" + 
                                                    "<Value Type='DateTime'>" + SPUtility.CreateISO8601DateTimeFromSystemDateTime(start) + "</Value>" + 
                                                "</Leq>" + 
                                                "<Geq>" +  
                                                    "<FieldRef Name='end'></FieldRef>" + 
                                                    "<Value Type='DateTime'>" + SPUtility.CreateISO8601DateTimeFromSystemDateTime(start) + "</Value>" + 
                                                "</Geq>" + 
                                            "</And>" + 
                                         "</Or>" + 
                                       "</And>"
                                   "</Where>"

    It checks just days, not hours or minutes.
    • Proposed as answer by Musta2 Friday, October 24, 2008 1:22 PM
    • Edited by Musta2 Friday, October 24, 2008 1:23 PM
    Friday, October 24, 2008 1:18 PM
  • Hi ,
    To include time in DateTime Query, you have to set IncludeTimeValue=”TRUE”.
     
    Ex: <Value Type='DateTime' IncludeTimeValue='True'></Value>

    Hope this helps :) .
    • Proposed as answer by Tester Thursday, April 23, 2009 6:33 AM
    Thursday, April 23, 2009 6:33 AM
  • buddy , check this out whole syntax including how to get the right format of datetime for CAML query http://muzms.blogspot.com/2010/01/datetime-value-comparison-in-caml-query.html
    Thursday, January 21, 2010 3:10 PM
  • This was very useful information - I was banging my head on this all day. The SPUtility method formatted the string correctly, but I needed to format the string without using SharePoint utilities. When I looked through the standard formats for the ToString method, I found this option:

    string camlQueryString = DateTime yourSampleDate.ToString("s");

    When I executed the query on SharePoint, it also worked just fine!
    Tuesday, January 26, 2010 2:09 PM
  • Hello

    The solution is already proposed by native CAML Query and  confirmed by "Tester"

    I normally use something like that :

     <Query>
        <Where>
             <Leq>
            <FieldRef Name='BeginDate' />
            <Value Type='DateTime'  IncludeTimeValue='TRUE'><Today /></Value>
            </Leq>
        <Where>
    </Query>

    Same will apply to  EndDate, etc

    Hope it helps Cheers :)

     


    Momo
    Friday, February 19, 2010 3:48 PM
  • Hello,

    <OrderBy><FieldRef Name='CreateDate' Ascending='False' /></OrderBy>

    I am not able to apply Order by on Date Column using CAML Query. Can anyone help how to order by in date column?

    Thanks in advance

    Friday, June 22, 2012 9:23 AM
  • <OrderBy><FieldRef Name='CreateDate' Type='DateTime' Ascending='False' /></OrderBy>

    Add Type='DateTime' attribute.

    Wednesday, July 18, 2012 8:07 PM
  • ViewFields>
          <FieldRef Name='Title' />
          <FieldRef Name='Popularity' />
       </ViewFields>
       <OrderBy>
          <FieldRef Name='Popularity' />
       </OrderBy>
       <Where>
          <Leq>
             <FieldRef Name='Created' />
             <Value Type='DateTime'>
                <Today Offset='-100' />
             </Value>
          </Leq>
       </Where>

    I am trying to fech data from discussion forum,the last 100 days count.but my query fails it shows all the records instead of last 100.I am working on sharepoint 2013 community template.Any help will be appreciated.Thanks

    Wednesday, September 04, 2013 5:54 AM
  • You are using <Leq>  (less than or equal to)  Try <Geq>  :)

    Ralph Burleson

    Friday, September 27, 2013 12:27 PM
  • I strongly recommend anybody who is encountering issues writing Caml Query the great tool SPCamlQuery helper http://spcamlqueryhelper.codeplex.com/.  

    U2U (in association with BIWUG) used to have a great tool, but can’t find it anymore.

    Wednesday, October 23, 2013 1:01 PM