CRM dates one day too early when accessing them via LINQ

For one of my a clients I had to make something to import records from their old invoicing system into the new CRM via csv files. When this was completed it had to export result data they would use to check if all the data was transferred correctly. Because they had to "practice" this import many times I decided to make a Windows Forms application and access the CRM data via the API using LINQ.

In the exported data I noticed that date fields came out one day too early. For example in the CRM interface the date was shown as 6/25/2015 but in my export files the dates showed up as 6/24/2015 which looked like very strange behavior to me.

After some investigation I noticed that date time fields in CRM are saved in the database as CET time. The project location was the Netherlands so the time difference with CET was 2 hours. When you have a CRM date only field it will send the time part along as 00:00:00 so when I entered 6/25/2015 in CRM it turned up as 6/24/2015 22:00:00 in the database.

So this way, when selecting date/time values via LINQ you come up one day short. This can be a nasty little problem that may cost you a lot of time to figure out.

Anyway, the solution for this problem is rather simple. Just convert the date time field ToLocalTime()

var contact = (from c in ContactSet
               where c.FullName == "Edwin Hebbink"
               select c).FirstOrDefault();

Just replace the "Edwin Hebbink" part with some contact name you have in your CRM and run this piece of code in LINQPad for CRM as a C# Statement and you will get the following result:

As you can see the first date is one day earlier then the second one.

If you want to read some more about CRM date/time issues this would be a very interesting article. In that article they talk about CRM 2011 but all this stuff still applies to the later versions as well.

Comments (3) -

  • article writing service

    1/4/2017 9:41:07 AM |

    I really want to get through the problems that I have with my laptop.


    1/28/2017 3:44:00 PM |

    That's great. I would like to get here more details on the topic. I think that some of those materials should be important for the understanding of the whole thing.

  • proofreading editing services

    2/8/2017 7:17:01 PM |

    Yes, I have this sort of a problem since. It was highly complicated for me. So, I've asked for a help.