Compare DateTime for daily, weekly and monthly duration within CAML Query

Question

I have 3 dates inserted in my list and they are:

1/1/2014 7:00 PM daily
12/2/2013 7:00 PM weekly
12/26/2013 7:00 PM monthly

I want CAML query in such a way that my current DateTime is 1/2/2014 6:22 PM. I want all these records selected as they are fall into next hour and duration given.
Please can anybody suggest me how to write query for this?

CAML Query as following:

<Query>
<Where>
  <Or>
     <And>
        <Eq>
           <FieldRef Name='DeliveryFrequency' />
           <Value Type='Choice'>Daily</Value>
        </Eq>
        <And>
           <Geq>
              <FieldRef Name='LastDeliveryTime' />
              <Value Type='DateTime'>[Today-1Day(s)]</Value>
           </Geq>
           <And>
              <Geq>
                 <FieldRef Name='LastDeliveryTime' />
                 <Value IncludeTimeValue='TRUE' Type='DateTime'>[Now-1Day(s)]</Value>
              </Geq>
              <Leq>
                 <FieldRef Name='LastDeliveryTime' />
                 <Value IncludeTimeValue='TRUE' Type='DateTime'>[Now+60Minute(s)]</Value>
              </Leq>
           </And>
        </And>
     </And>
     <Or>
        <And>
           <Eq>
              <FieldRef Name='DeliveryFrequency' />
              <Value Type='Choice'>Weekly</Value>
           </Eq>
           <And>
              <Eq>
                 <FieldRef Name='LastDeliveryTime' />
                 <Value Type='DateTime'>[Today-7Day(s)]</Value>
              </Eq>
              <And>
                 <Geq>
                    <FieldRef Name='LastDeliveryTime' />
                    <Value IncludeTimeValue='TRUE' Type='DateTime'>[Now-7Day(s)]</Value>
                 </Geq>
                 <Leq>
                    <FieldRef Name='LastDeliveryTime' />
                    <Value IncludeTimeValue='TRUE' Type='DateTime'>[Now-7Day(s)+60Minute(s)]</Value>
                 </Leq>
              </And>
           </And>
        </And>
        <And>
           <Eq>
              <FieldRef Name='DeliveryFrequency' />
              <Value Type='Choice'>Monthly</Value>
           </Eq>
           <And>
              <Eq>
                 <FieldRef Name='LastDeliveryTime' />
                 <Value Type='DateTime'>[Today-1Month(s)]</Value>
              </Eq>
              <And>
                 <Geq>
                    <FieldRef Name='LastDeliveryTime' />
                    <Value IncludeTimeValue='TRUE' Type='DateTime'>[Now-1Month(s)]</Value>
                 </Geq>
                 <Leq>
                    <FieldRef Name='LastDeliveryTime' />
                    <Value IncludeTimeValue='TRUE' Type='DateTime'>[Now-1Month(s)+60Minute(s)]</Value>
                 </Leq>
              </And>
           </And>
        </And>
     </Or>
  </Or>

This is my query works fine with CAML query builder but in SPQuery it does not return any record. I have tried by providing ViewAttributes to "RecursiveAll". What is wrong with given code?

http://stackoverflow.com/questions/4648517/datetime-comparaison-in-caml-query-for-sharepoint — Iman, Apr 05 '14 at 05:05

score 1 · Accepted Answer · edited Apr 05 '14 at 05:00

I have solved this error by my own. I have removed Inbuilt function like today and now with my own value from code side.

  Microsoft.SharePoint.Utilities.SPUtility.CreateISO8601DateTimeFromSystemDateTime(
DateTime.Now.AddMonths(-1))

More Complete Sample

string queryString_getPersonalAlerts = string.Format(@"<Where><Or>
            <And><Eq><FieldRef Name='{0}' />
            <Value Type='Choice'>Daily</Value></Eq><And><Geq><FieldRef Name='{1}' />
            <Value Type='DateTime'>{2}</Value></Geq><And><Geq><FieldRef Name='{1}' />
            <Value IncludeTimeValue='TRUE' Type='DateTime'>{2}</Value></Geq><Leq><FieldRef Name='{1}' />
            <Value IncludeTimeValue='TRUE' Type='DateTime'>{3}</Value></Leq></And></And></And><Or><And><Eq><FieldRef Name='{0}' />
            <Value Type='Choice'>Weekly</Value></Eq><And><Eq><FieldRef Name='{1}' />
            <Value Type='DateTime'>{4}</Value></Eq><And><Geq><FieldRef Name='{1}' />
            <Value IncludeTimeValue='TRUE' Type='DateTime'>{4}</Value></Geq><Leq><FieldRef Name='{1}' />
            <Value IncludeTimeValue='TRUE' Type='DateTime'>{5}</Value></Leq></And></And></And><And><Eq><FieldRef Name='{0}' />
            <Value Type='Choice'>Monthly</Value></Eq><And><Eq><FieldRef Name='{1}' />
            <Value Type='DateTime'>{6}</Value></Eq><And><Geq><FieldRef Name='{1}' />
            <Value IncludeTimeValue='TRUE' Type='DateTime'>{6}</Value></Geq><Leq><FieldRef Name='{1}' />
            <Value IncludeTimeValue='TRUE' Type='DateTime'>{7}</Value></Leq></And></And></And></Or></Or></Where>",    
         PersonalAlerts.Fields.DeliveryFrequency.ToString(),     
        PersonalAlerts.Fields.LastDeliveryTime.ToString(),
    SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.Now.AddDays(-1)), 
        SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.Now.AddDays(-1).AddMinutes(60)), 
SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.Now.AddDays(-7)), 
        SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.Now.AddDays(-7).AddMinutes(60)), 
        SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.Now.AddMonths(-1)), 
        SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.Now.AddMonths(-1).AddMinutes(60)));

Compare DateTime for daily, weekly and monthly duration within CAML Query

1 Answers1