Saturday, 6 May 2017

MSCRM - FetchXML filter based on time and time zone


Recently came across a scenario where we had to query CRM and retrieve accounts based on UTC date. After a fair bit of research, came-up with a Fetch query that can retrieve records based on time and time zone filters.

The key part in querying is the ISO 8601 format to specify the date time with timezone.

In the below date, +00.00 stands for UTC Time Zone and we can use any timezone by modifying this part.
2017-05-05T22:03:19+00:00

Below is a sample fetchxml, which retrieve records with modifiedon greater than or equal to given UTC datetime.

Note: Use 'greater than or equal to' or 'lesser than or equal to' instead of 'on or before' or 'on or after'

 <fetch>  
   <entity name="account" >  
     <all-attributes/>  
     <filter type="and" >  
       <condition attribute="modifiedon" operator="ge" value="2017-05-05T22:03:19+00:00" />  
     </filter>  
   </entity>  
 </fetch>  

To filter time based on user's timezone remove the timezone part(+00:00) from the query and the sample will look like below,

 <fetch>  
   <entity name="account" >  
     <all-attributes/>  
     <filter type="and" >  
       <condition attribute="modifiedon" operator="ge" value="2017-05-05T22:03:19" />  
     </filter>  
   </entity>  
 </fetch>


Hope this helps!

No comments:

Post a Comment