CODEWITHSUNDEEP
c#linqdatetimedynamics-crm-2013dynamics-crm-online
Chris
Chris

Reputation: 67

Microsoft CRM Online - LINQ query when comparing date to local date

I'm working with the Microsoft CRM Online SDK for a project that I'm working on. One part of this is developing a mobile application that queries using LINQ for displaying data to the web application.

var InspectionSchedule = xrm.new_inspectionsSet
              .Where(i => i.new_InspectionDate.Equals(System.DateTime.UtcNow));

The web application will be run in the same timezone as it's users which is Australia QLD Time. Microsoft CRM SDK pulls the datetime as UTC whereas my timezone is 10 hours ahead.

What I'm trying to do is query the CRM Entity (Inspections) and convert to my local timezone and then query what records to display for today (in Australian Timezone).

Any ideas how I convert to say DD/MM/YYYY for both - in the same timezone (i.e. the UTC date from CRM is converted and compared to local datetime).

Upvotes: 0

Views: 1614

Answers (2)

Mauro De Biasio
Mauro De Biasio

Reputation: 1146

crm Online is a little bit tricky I found the same problem, basically from the online org you are not able to see the timezone. I'm from Australia too, and I have a linq query to compare dates, I had the same problem. The linq query gets the dates in UTC, but I'm not able to convert them properly.

Let's say that i need to check the first of the month, (for australia time UTC+10). If i save a date via UI and i save the 1st of the month in the db will be:

31/12/2014 02:00:00 PM (UTC +0)

instead of:

01/01/2015 12:00:00 AM (UTC +10)

But when but when i do the comparison the linq query is picking the UTC date, and I'm not able to get the correct timezone from the crm online. If I try to recover the timezone using the DateTime class, i get just empty. The only way that i found was to statically move the UTC date one day behind to be ablt to query the system correctly (for me doesn't have much importance since the date will always be the 1st of the month)

Sorry I forgot about the reply, In your case you are comparing a datetime (probably set and dd/MM/yyyy in the crm with a datetime dd/MM/yyyy HH:mm:ss tt. The operation that your linq query is doing is correct, but maybe you shouldn't use the equals. What i mean is, a user saves an appointment from Rome to be in Rome time (UTC +1) at 8:00 am tomorrow (now is 9:00pm for instance). In the db this will be saved as tomorrow 7:00 am, now a user from Australia (UTC +10) needs to know if they have an appointment (is 11:00am in australia now, UTC 1:00am), they just need a filter on the day, because whatever an australian is doing on the crm they will see (7 am UTC as 17:00 pm Sydney time). In conclusion the filter should work correctly, only if you are doing operations of insertion you will need to remember to push the data to the CRM as UTC not as local time, otherwise the crm will move the time based on your user timezone.

Upvotes: 0

jessehouwing
jessehouwing

Reputation: 114796

You can convert between UTC and Local quite easily using .ToLocalTime() and .ToUniversalTime(). This works when you application is running in the target timezone.

You need to be careful when using these functions around the moment your timezone changes between Summer and Winter time (if your timezone follows the daylight savings rules). There are times of day that may be Ambiguous in the local timezone (3am occurring twice for example).

If you want to have more control over the conversions, you can use the TimeZone class to convert from the source zone to UTC and then to the target timezone.

There is a whole doc on MSDN which covers timezone and UTC conversions in depth, I recommend you read it all.

Upvotes: 1

Related Questions