locked
DateTime FetchXML Filter not working as expected RRS feed

  • Question

  • I'm running a plugin with should retrieve accounts that were modified after a certain time. It appears the modified on field is not using the time aspect - is this expected? My filter:

    <fetch version='1.0' output-format='xml-platform' mapping='logical' distinct='false'>
      <entity name='account'>
        <attribute name='name' />
        <attribute name='address1_line1' />
        <attribute name='modifiedon' />
        <attribute name='telephone1' />
        <attribute name='websiteurl' />
        <filter type='and'>
          <filter type='or'>
            <condition attribute='modifiedon' operator='null' />
            <condition attribute='modifiedon' operator='gt' value='9/5/2014 9:33:33 AM' />
          </filter>
          <condition attribute='tickersymbol' operator='not-null' />
        </filter>
      </entity>
    </fetch>

    The resulting account is:

     <account>
        <name>Blue Yonder Airlines (sample)</name>
        <address1_line1>9068 Muir Road</address1_line1>
        <modifiedon>9/5/2014 9:31:05 AM</modifiedon>
        <telephone1>555-01543</telephone1>
        <websiteurl>http://www.blueyonderairlines.com/</websiteurl>
        <accountid>bcd0a989-482e-e411-a7f7-6c3be5be1fec</accountid>
        <address1_composite>9068 Muir Road</address1_composite>
      </account>

    Which I would not expect to be returned as the modified on is 3 minutes before the query.

    It could be a time zone issue (I'm BST at the moment) but both are displayed as 10:33 and 10:30 so both are converted to UTC.


    • Edited by ChrisJC119 Friday, September 5, 2014 9:42 AM spelling
    Friday, September 5, 2014 9:41 AM

All replies

  • The query result will always be in UTC. A date in the request will however be treated as local time. So if BST the time will be converted to 8:33:33.

    You could use a variable for the DateTime and convert it to local (while BST this will be 10:33:33). CRM will convert this value to UTC being 9:33:33.

    • Proposed as answer by Jan AS Monday, September 8, 2014 1:31 PM
    Sunday, September 7, 2014 8:52 AM
  • Hi,

    Thanks for the answer. If both date/times are in UTC, why is the query 'greater than 9:33:33 AM' returning the record with '9:31:05 AM'? Or am I missing something?

    Thanks

    Monday, September 8, 2014 8:22 AM
  • As I said, your request DateTime is in BST. The response is in UTC.

    Try 10:33:33 in your request and you will no longer get Blue Yonder...

    Monday, September 8, 2014 1:34 PM