How to filter on the Time part of a date in FetchXML
Views (155)
Like
Share
Report
Did you try to set a filter on a date ? Pretty easy, isn't it ?
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.
And with a time part ? Harder, right ?
This post will help you to make it easy !
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 :
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.
This was originally posted here.
*This post is locked for comments