Local DateTime CRM Online SSRS Report

Local DateTime in UK could get a bit tricky because of the summer time, where it is one hour less in UTC time.  This post will help you to find out the local Date.

There is a simple solution for this: Use the date only field from the dataset. When the CRM date attribute (e.g. “MyDate”) is declared in the Fetch XML query of your dataset, you get two SSRS fields in that dataset: MyDate and MyDateValue. The first one is a date only field in local time, so you can do something like Fields!MyDate.Value. The second one is a datetime field in UTC time. You are probably interested in the first one 😉

If you also want to force a UK time format, you can use the next expression:

=iif(IsNothing(Fields!MyDate.Value),

Nothing,

CDate(iif(IsNothing(Fields!MyDate.Value), 1, Mid(Fields!MyDate.Value, 4, 2)) &

“/” & iif(IsNothing(Fields!MyDate.Value), 1, Mid(Fields!MyDate.Value, 1, 2)) & “/” & iif(IsNothing(Fields!MyDate.Value), 1, Mid(Fields!MyDate.Value, 7, 4))))

Just to say thanks to my colleague Mario (@mtcantero // http://crmandcoffee.wordpress.com/) for his input in this article.

Advertisements

Posted on September 16, 2013, in CRM, CRM Online, SSRS and tagged , , . Bookmark the permalink. Leave a comment.

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

%d bloggers like this: