CRM behaves a bit differently when it comes to DateTime. While all the DateTime field in the CRM Client(UserInterface) displays User’s local time, it is stored in the Database as UTC time (Coordinated Universal Tme).
Lets take an Example:
User’s local time(INDIA) = UTC + 5:30 Hrs
In INDIA, If you are working late in night and create a task activity on 14th Sep at 1:00 AM, then even though the Created-On field will show value as 14Sep – 1:00AM (User’s local time) in the CRM UI but in Database Created-On will be saved as 13Sep – 19:30PM (UTC).
Also when you use Only a Date field in CRM, then also while selecting the Date field as 14Sep(it is read as 14Sep 12:00AM) and in Database it is saved as 13Sep 18:30PM.
In-spite the fact Microsoft has provided a great in-build reporting tool within CRM but many a times we need to build complex reports using SSRS. So if you go for a SQL report where you want the count of activities from 14th Sep, probably you will miss the Count of no. of activities created before 5:30 AM on 14th Sep. (A nightmare for people who work late in night like me.. 😛 )
I have found a solution to this issue, where I usually use a DateAdd() function whenever I have DateTime as a parameter in SQL Query.
Select Subject, Description, CreatedByName, RegardingObjectIdName from Task where CreatedOn between DATEADD(minute,-330, @From_Date) and DATEADD(minute,-330, @To_Date)
Where by subtracting 330 minutes(5:30 Hrs) from User’s local time makes it equal to the UTC time as stored in DB.
Hope this will be Helpful. Kindly share if you have any standard or better solution to this.