none
How to use DateTime field in CAML Query

    问题

  • Hello all,

    I have created a caml query and need to get the values only of "2012-06-28" date. But it is not giving the proper result. I think i have to remove time part from datetime field but i dont know how. Can any one please correct if i am missing anything ???, Thanks in advance.

    <Query>
       <Where>
          <And>
             <Gt>
                <FieldRef Name='Created' />
                <Value IncludeTimeValue='TRUE' Type='DateTime'>2012-06-27T18:41:53Z</Value>
             </Gt>
             <Lt>
                <FieldRef Name='Created' />
                <Value IncludeTimeValue='TRUE' Type='DateTime'>2012-06-29T18:42:09Z</Value>
             </Lt>
          </And>
       </Where>
    </Query>

    2012年6月28日 9:25

答案

  • Ah, i see the evil part :D

    Remove the <Query> element, it should not be there. It kills the query.

    Also, if you wish to use today date, don't use Lt and Gt, use Leq and Geq (to include that date).


    Please don't forget to mark the post as Helpful if you find my comment useful and as Answer if it solved your problem.
    INFOTEHNA Group, Document and Process Management Solutions ( www.infotehna.com )

    • 已标记为答案 Rauf Ab 2012年7月2日 10:14
    2012年6月28日 10:15

全部回复

  • Hello,

    I'd say, remove the IncludeTimeValue since you don't need it for date, and make sure you transform comparison datetime to ISO, using method Microsoft.SharePoint.Utilities.SPUtility.CreateISO8601DateTimeFromSystemDateTime()   - use that instead of yout time (eg. 2012-06-29T18:42:09Z).

    e.g.

    "<Value Type='DateTime'>"+SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.UtcNow)+"</Value>"


    Please don't forget to mark the post as Helpful if you find my comment useful and as Answer if it solved your problem.
    INFOTEHNA Group, Document and Process Management Solutions ( www.infotehna.com )


    2012年6月28日 9:48
  • Thanks Mladen,

    Result is still the same, it actually display all the rows in the list while i only need to see the "2012-06-28" date values. Can you please tell me iam missing and how can i correct my spquery ???. Following is the complete query, Thanks.

      Dim query = New SPQuery
            Dim dt As DataTable
            Dim VDate As String = SPUtility.CreateISO8601DateTimeFromSystemDateTime(DateTime.UtcNow)
            query.Query = "<Query><Where><And><Gt><FieldRef Name='Created' /><Value Type='DateTime'>" +
    VDate + "</Value></Gt><Lt><FieldRef Name='Created' /><Value Type='DateTime'>" + VDate + "</Value></Lt></And></Where></Query>"
            '2008-01-01T00:00:00Z
            Dim list As SPList = web.Lists("LeaveApplication")
            dt = list.GetItems(query).GetDataTable

            Label1.Text = CType(dt.Rows.Count, String)

            GridView1.DataSource = dt
            GridView1.DataBind()

    2012年6月28日 10:06
  • Ah, i see the evil part :D

    Remove the <Query> element, it should not be there. It kills the query.

    Also, if you wish to use today date, don't use Lt and Gt, use Leq and Geq (to include that date).


    Please don't forget to mark the post as Helpful if you find my comment useful and as Answer if it solved your problem.
    INFOTEHNA Group, Document and Process Management Solutions ( www.infotehna.com )

    • 已标记为答案 Rauf Ab 2012年7月2日 10:14
    2012年6月28日 10:15
  • Thanks Mladen,

    Following query is working fine if i need to display values only for one day. But if i need to filter values for one month then how will you update/change my following query ???, thanks

    query.Query = "<Where><Eq><FieldRef Name='Created' /><Value Type='DateTime'>2012-06-28T20:22:23Z</Value></Eq></Where>"

    2012年6月28日 10:26
  • use the daterangeoverlap something like

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

    but some say it has bugs though i havent seen any yet... you can also it like

    <Where><And><Geq><FieldRef Name='Created' /><Value Type='DateTime'>2012-05-28T20:22:23Z</Value></Geq><Leq><FieldRef Name='Created' /><Value Type='DateTime'>2012-05-28T20:22:23Z</Value></Leq></And></Where>


    See is this helps


    "T" | My blog updates| My Twitter | Our Products | Mail Me | LinkedIn | My Virtual Business Card

    A THOUGHT ABOUT MY WORKPLACE :)


    2012年6月28日 11:10
  • If you meant range of dates (and not specific) you can use what you used.... <And> in combination with <Geq> and <Leq> dates.

    If you with all dates in specific month (or current), you can use that with some recycling of code on this page http://www.c-sharpcorner.com/UploadFile/scottlysle/FirstAndLastDay10262007135750PM/FirstAndLastDay.aspx .

    Tanmay's overlapping also is a valid option.


    Please don't forget to mark the post as Helpful if you find my comment useful and as Answer if it solved your problem.
    INFOTEHNA Group, Document and Process Management Solutions ( www.infotehna.com )

    2012年6月28日 12:30
  • Hello,

    Pleae look at the following line of query. I didn't understand the bold line of query and what is the meaning of Z in the last word of query, thanks ???

    Value Type='DateTime'>2012-05-28T20:22:23Z

    2012年6月28日 13:16
  • This is a date time formatting iso standart.

    Z - means end of the string (like in C++ lpsz notation) :)


    LinkedIn Profile

    2012年6月28日 13:54