CAML Query not ordering properly

camlsharepoint

Can anyone help me with this CAML query? When I flip the Ascending attribute from TRUE to FALSE (have also tried True and False), it doesn't re-order the result set.

The rest of the CAML is correct, it is being generated by a tool and the appropriate results are being returned.

<Where>
  <And>
    <And>
      <Eq>
        <FieldRef Name="Branch"/>
        <Value Type="Text">Camp 1</Value>
      </Eq>      
      <Eq>
        <FieldRef Name="Type"/>
        <Value Type="Choice">Day</Value>
      </Eq>
    </And>
    <Geq>
      <FieldRef Name="StartDateTime"/>
      <Value Type="DateTime">2009-01-05T00:00:00Z</Value>
    </Geq>
  </And>
  <OrderBy>
    <FieldRef Ascending="TRUE" Name="Title" />
  </OrderBy>
</Where>

Best Answer

Doesn't the OrderBy have to be outside the Where clause?

    <Where>
  <And>
    <And>
      <Eq>
        <FieldRef Name="Branch"/>
        <Value Type="Text">Camp 1</Value>
      </Eq>      
      <Eq>
        <FieldRef Name="Type"/>
        <Value Type="Choice">Day</Value>
      </Eq>
    </And>
    <Geq>
      <FieldRef Name="StartDateTime"/>
      <Value Type="DateTime">2009-01-05T00:00:00Z</Value>
    </Geq>
  </And>
  </Where>
<OrderBy>
    <FieldRef Ascending="TRUE" Name="Title" />
  </OrderBy>

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

Related Solutions

SharePoint – all day events behave differently in CAML query

SharePoint stores the date/time in UTC (aka GMT or Zulu) and when displaying first converts it to the sites local time zone.

However for All day events it stores the times (00:00 to 23:59:00) in the sites LOCAL time zone instead.

As you've already figured out yourself - I believe you've found a bug in the way the SharePoint interprets the query and its forgetting that all day events are local time.

I guess you could do a nasty workaround this by doing a query for

EventDate >= SomeDate OR AllDayEvent = True AND EventDate >= SomeDate - 4hrs

This poster has similar problems SO - SharePoint all day event gives obscure result

And this will give you some more insight into how borked time zones are in SharePoint SharePoint Web Services and UTC time fun and games

And if that's not enough frustration for ya then look at Created/Modified dates via the object model and marvel at how they are reported as Local time for normal events and UTC for all day events!

SharePoint List View – DateTime in the Query String

Have you tried adding a custom page and then adding a DataFormWebPart (DFWP) to it? That in turn would allow you to shape your CAMl query in the SelectCommand of the SPDatasource used by the DFWP, using actual ASP.NET Calendar Controls to be used as parameters, specified in the SPDataSource's parameters. Use Control(ID, PROPERTYTOUSEINCAML) in the parameterbindings of the spdatasource.

i.e.:

<ParameterBinding Name="StartDate" Location="Control(calStart, SelectedDate)" DefaultValue="01-01-2009"/>
<ParameterBinding Name="EndDate" Location="Control(calEnd, SelectedDate)" DefaultValue="01-01-2009"/>

then have the SelectCommand's CAML be something like:

<Where>
  <And>
    <Geq>
      <FieldRef Name="Event_x0020_Start_x0020_Date" />
      <Value Type=”DateTime”>{StartDateParameter}</Value>
    </Geq>
    <Leq>
      <FieldRef Name="Event_x0020_Start_x0020_Date" />
      <Value Type=”DateTime”>{EndDateParameter}</Value>
    </Leq>
  </And>
</Where>

Best Answer

Related Solutions

SharePoint – all day events behave differently in CAML query

SharePoint List View – DateTime in the Query String

Related Topic