Gotcha: DateTimeKind and FetchXml

I was going through the FetchXML schema and found these two interesting attributes. The first one is “utc-offset” on the “fetch” node.

utcoffset

The other one is “usertimezone” on the “attribute” node.

usertimezone

Both these attributes give an impression, that it is possible to return datetime attributes in a different timezone instead of UTC, but in reality they don’t seem to do anything.

The only way to return datetime in the user’s local timezone, is by using the deprecated ExecuteFetchRequest. I will demonstrate this with XrmToolBox. This is my simple fetch query.

fetchxml-with-datetime

Below is the raw fetch result

fetch-xml-response-raw

Below is the table grid result

fetch-xml-response-retrievemultiple

I execute the FetchXML using the FetchXML Builder tool. As you can see, the datetime values are different. This is because when you use “RetrieveMultiple” to execute the fetch query, the datetime returned is always UTC and has to be converted to the local timezone. ExecuteFetchRequest is now deprecated and RetrieveMultiple is the recommended way to execute fetchxml. These two points really puzzle me:

  1. Why user-timezone and utc-offset don’t seem to do anything. Are they internally used?
  2. Why MS decided to change the datetime behaviour

So, this is a good point to remember when you are executing fetchxml using “FetchXML Tester” or have set the Result view to Raw fetch result in “FetchXML Builder” or using a mixture of ExecuteFetchRequest and RetrieveMultiple in your code.

References:

  1. ExecuteFetchRequest class
  2. XrmToolBox Issue #326

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s