Wednesday, August 5, 2015

How to filter on the Time part of a date in FetchXML

Did you try to set a filter on a date ? Pretty easy, isn't it ?
And with a time part ? Harder, right ?
This post will help you to make it easy !
For example, what does this FetchXML query will return ?
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="account">
    <attribute name="name" />
    <attribute name="address1_city" />
    <attribute name="primarycontactid" />
    <attribute name="telephone1" />
    <attribute name="accountid" />
    <order attribute="name" descending="false" />
    <filter type="and">
      <condition attribute="statecode" operator="eq" value="0" />
      <condition attribute="modifiedon" operator="on-or-before" value="2015-08-05" />
    </filter>
  </entity>
</fetch>
It will return all the records modified on or before the 5th August 2015.

If you want to get only those modified on or before 12:00 AM on the 5th August 2015, you won't be able to achieve this through the Advanced Find because you can't specify a time part. And, even if you try to replace "2015-08-05" by "2015-08-05T12:00:00", it will still return the records modified between 12:00 AM and 12:00 PM.
The solution is to use the "Less or equal" operator instead of the "On or before" operator. It's a numerical operator but it also works with DateTime values.

Here is an example :
<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
  <entity name="account">
    <attribute name="name" />
    <attribute name="address1_city" />
    <attribute name="primarycontactid" />
    <attribute name="telephone1" />
    <attribute name="accountid" />
    <order attribute="name" descending="false" />
    <filter type="and">
      <condition attribute="statecode" operator="eq" value="0" />
      <condition attribute="modifiedon" operator="le" value="2015-08-31T12:00:00+02:00" />
    </filter>
  </entity>
</fetch>

Obviously, you can use the other numerical operators !

Bonus part : Notice the UTC timezone, I'm in France and it's summer therefore my timezone is UTC+2.
The DateTime value are timezone sensitive so don't forget to put it in your FetchXML queries !

You can check on Wikipedia for ISO 8601 DateTime standard used in MS CRM.