locked
fetchXML order by date RRS feed

  • Question

  • Hi,

      I am trying the sort the items just by StartDate which is a DateTime field, I would like to know how can I sort it just by DAY not with DateTime

    SQL

    SELECT       starttime 
    FROM            table1
    WHERE        (invoicename = 'INV-1111')
    ORDER BY day(starttime) DESC

    FetchXML

    <fetch mapping="logical">
      <entity name="table1">
        <attribute name="starttime" />

     <order attribute='starttime' descending='true' />

        <filter>
          <condition attribute="invoicename" operator="eq" value="INV-1111" />
        </filter>
      </entity>
    </fetch>

    Any help would be much appreciated

    Shwetha


    Thursday, December 5, 2013 1:45 AM

All replies

  • Hello,

    Unfortunately you would not be able to do it directly. To make it possible you will have to do following steps:

    1. Create int field (for example new_day).

    2. Write a plugin that would be triggered everytime starttime field is changed or record is created and fill new_day field.

    3. Use new_day field in your query.


    Dynamics CRM MVP/ Technical Evangelist at SlickData LLC
    My blog

    Thursday, December 5, 2013 1:53 AM
    Moderator
  • Thanks for the response

    but I cannot created/modify CRM db, I can only execute select queries.

    Thursday, December 5, 2013 2:07 AM
  • In this case you would not be able to achieve your goal, sorry.

    Dynamics CRM MVP/ Technical Evangelist at SlickData LLC
    My blog

    Thursday, December 5, 2013 2:19 AM
    Moderator
  • Shwetha,

    There are some workaround like Andrii mentioned.

    If you need this for reporting or JS purposed then you can use expression or JS functions to retrieve day from datetime.

    Unfortunately fetchXml is very limited and you can only sort by datetime.

    KG 




    My Dynamics CRM Blog: http://bovoweb.blogspot.com

    Thursday, December 5, 2013 2:01 PM