Microsoft CRM 2013, SSRS, and Date Parameters

The Situation: Said favorite client called me up and said that when he ran a report (it was a SQL Server Reporting Services report he accessed via the Outlook plugin), he was getting improper results.  This particular report had start and end date as parameters and if he put in a start date of 1/1/2014, some records weren’t appearing even though the date in question was on 12/31/2013.  The records did appear if he changed the date to 1/2/2014. The Solution: Thankfully, we figured out what was happening pretty quickly. For better or for worse, I had had to do some updates directly to the client’s CRM database (yes, that’s a major no-no, but previous decisions and some unique situations forced my hand.)  Some of those updates involved artificially changing a date that drove my client’s reporting criteria. I didn’t think about it at the time, but when I was updating those dates, if I just updated them to a day (say 1/1/2014 00:00:00), the rows didn’t show up if the start date was 1/1/2014.  They also didn’t appear when I set the time to 1/1/2014 04:00:00.  However, they did appear on the report if I updated the date to 1/1/2014 05:00:00.  I should have dug more into why that was, but I never had the time. However, once the same issue appeared in a different place on the report, I suddenly found I needed to find the time to figure out what’s happening.  At this point, my best guess is that my client is being bitten by something I wrote on a while back in the way Microsoft CRM handles dates. Sadly, CRM 2011 is inconsistent in its date interpretation.  Sometimes, when CRM sees a timestamp, it will think it’s looking at GMT time and at other times, it will decide that it’s looking at local time. If SQL Server or CRM believes that the timestamps in the database are local time, they’re going to translate that local time to GMT.  So, if the date in the database is 1/1/2014 00:00:00 and I’m in the Eastern Time Zone, once CRM changes the timestamp to adjust to GMT, the date becomes 12/31/2014.  However, if the date is 1/1/2014 05:00:00, once the timestamp is adjust, it’s still 1/1/2014. So, updating the dates to 5AM was a total accident, but it made the report work. I don’t know that there’s a general principle here to apply to all reports or SQL, but it’s something to check.  When you have rows not appear in a SSRS report, check the date parameters.  You may be in a completely different time zone than you think.