SSRS Reporting Date Formats from CRM Online

Key Technologies:  SQL Server Reporting Services, CRM Online

1. Create a report in SSRS. Configure data source and credentials.
2. Query the dataset and map the fields on the table.

Date format on the report needs to be displayed as it is in CRM (“dd/MM/yyyy“).

But the problem is when we use fetchXML to retrieve the data, datetime field returns 2 fields for my dataset (one value is datetime in UTC and one value is string datetime in local user setting).

So we need to convert the string value to datetime, but ‘CDate’ gives an error because it is presuming that the date is always in US format(MM/dd/yyyy) like 24/07/2012 is an invalid date  for CDate because 24 is an invalid month.

3. Similarly, we can’t use the Format date function, need to convert it to datetime first.

For any reports SSRS interprets all of dates as US format Ex: 06/03/2013. Actual date is 6 March 2013 but in SSRS it is displayed as 3 June 2013 which is incorrect as client uses date format in dd/MM/yyyy

4. The solution is to change the language properties in the report to specific region, which  is set to en-US by default.

Go to the Report properties and for Language property try setting the value of Language to “=User!Language”