Share via

CAML Order by date

Question

Thursday, September 26, 2013 12:39 PM

Hi, I have a CAML that should be ordered by date but it seems that it only considers the day to order the results so 20/02/2013 would come before 23/01/2013 for example. Anyone knows how to solve that pls? 

thanks

All replies (4)

Thursday, September 26, 2013 3:28 PM âś…Answered

From your query, the results returned from currentWeb.GetSiteData should be sorted by the ArticleStartDate.

Is ArticleStartDate a date field or text field? I'm assuming ArticleStartDate is a text field, and the sort is performed at textual level, hence 20/02/2013 coming before 23/01/2013.

Regards, Matthew
MCPD | MCITP
My Blog
Please remember to click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if it was useful.

See my webpart on the TechNet Gallery that allows administrative users to upload, crop and format user profile photos. Check it out here: Upload and Crop User Profile Photos

Thursday, September 26, 2013 12:53 PM

What does your CAML query look like? What are the regional settings on the server?

You should be able to order by a date field using CAML like this, which orders on the Modified field, with the most recently modified item appearing first.

<Query>
    <OrderBy>
        <FieldRef Name="Modified" Ascending="FALSE"/>
    </OrderBy>
</Query>

Regards, Matthew
MCPD | MCITP
My Blog
Please remember to click "Mark As Answer" if a post solves your problem or "Vote As Helpful" if it was useful.

See my webpart on the TechNet Gallery that allows administrative users to upload, crop and format user profile photos. Check it out here: Upload and Crop User Profile Photos

Thursday, September 26, 2013 2:12 PM

Hi Matthew, yes it should work normally, there is the code:

List<Medias> res;
SPSiteDataQuery allItemsSiteQuery = new SPSiteDataQuery();
allItemsSiteQuery.RowLimit = (uint)rowLimit;
allItemsSiteQuery.ViewFields = "<FieldRef Name='Title' />" +
                                "<FieldRef Name='WebTVs_startDate' />" +
                                "<FieldRef Name='WebTVs_endDate' />" +
                                "<FieldRef Name='ArticleStartDate' />" +
                                "<FieldRef Name='WebTVs_remontee' />" +
                                "<FieldRef Name='WebTVs_urlimage' />" +
                                "<FieldRef Name='WebTVs_miniature' />" +
                                "<FieldRef Name='WebTVs_theme' />" +
                                "<ProjectProperty Name='Title' />" +
                                "<ProjectProperty Name='Url' />" +
                                "<FieldRef Name='WebTVs_surtitre' />" +
                                "<FieldRef Name='FileRef' />";
allItemsSiteQuery.Lists = "<Lists ServerTemplate='850'/>";
allItemsSiteQuery.Webs = "<Webs Scope='Recursive' />";
allItemsSiteQuery.Query = string.Format("<OrderBy><FieldRef Name='ArticleStartDate' Ascending='False' /><FieldRef Name='ID' Ascending='False'/></OrderBy><Where><And><And><And><And><And><Or><And><And><Leq><FieldRef Name='WebTVs_startDate'/><Value Type='DateTime'><Today /></Value></Leq><Gt><FieldRef Name='WebTVs_endDate'/><Value Type='DateTime'><Today /></Value></Gt></And><Leq><FieldRef Name='WebTVs_startDate'/><Value Type='DateTime'><Today /></Value></Leq></And><IsNull><FieldRef Name='WebTVs_endDate'/></IsNull></Or><IsNotNull><FieldRef Name='WebTVs_startDate'/></IsNotNull></And><Eq><FieldRef Name='WebTVs_remontee'/><Value Type='Boolean'>False</Value></Eq></And><Eq><FieldRef Name='WebTVs_remontee'/><Value Type='Boolean'>False</Value></Eq></And><BeginsWith><FieldRef Name='ContentTypeId'/><Value Type='Text'>{0}</Value></BeginsWith></And><BeginsWith><FieldRef Name='ContentTypeId'/><Value Type='Text'>{0}</Value></BeginsWith></And></Where>", ArticleContentTypeId);
res = currentWeb.GetSiteData(allItemsSiteQuery).Rows.Cast<DataRow>().Select(row => new Medias(
    int.Parse(row["ID"] as string),
    row["Title"] as string,
    TryGetShortDateString(row["ArticleStartDate"] as string),
    row["WebTVs_urlimage"] as string,
    row["WebTVs_miniature"] as string,
    row["ProjectProperty.Title"] as string,
    row["FileRef"] as string,
    row["WebTVs_surtitre"] as string,
    row["FileRef"] as string
)).ToList();

Thursday, September 26, 2013 2:20 PM

I see!!! so the ordering has to be done in:

res = currentWeb.GetSiteData(allItemsSiteQuery).Rows.Cast<DataRow>().Select(row => new Medias(
    int.Parse(row["ID"] as string),
    row["Title"] as string,
    TryGetShortDateString(row["ArticleStartDate"] as string),
    row["WebTVs_urlimage"] as string,
    row["WebTVs_miniature"] as string,
    row["ProjectProperty.Title"] as string,
    row["FileRef"] as string,
    row["WebTVs_surtitre"] as string,
    row["FileRef"] as string
)).ToList();

do you know how to order thsi way? thanks