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.

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

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.

Below is the raw fetch result

Below is the table grid result

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