CRM 2011 Custom page and SQL TimeZone Issue

Question

I have a custom aspx application that directly queries the CRM database base tables such as ActivityPointer to get the data such as due date, created on etc. The custom aspx page need to filter data based on certain date range such as for today, last 7 days etc. It does so by executing custom sproc stored in CRM database. However I am facing challenge with the user timezone issue getting the correct data.

DECLARE @StartDate DATETIME;
DECLARE @EndDate DATETIME;
DECLARE @NoOfDay INT= 7 -- passed as param
SET @StartDate = DATEADD(day, DATEDIFF(day, @NoOfDays-1, GETDATE()), 0)
SET @EndDate = DATEADD(day, DATEDIFF(day, - 1, GETDATE()), 0)

This will always return the startdate and enddate based on the machine timezone where the sql instance is running. What would be the correct approach to set the start date/end date so it also considers user timezone settings? I don't think I can use CRM specific functions such as dbo.fn_UTCToLocalTime() etc because my custom aspx will be using a different account to access/execute the sproc.

Daryl Daryl · Accepted Answer · 2013-08-16T19:52:42

Aside from maybe a direct SQL call being a little overkill for this, I'd just change the SP to accept a start and end date, and send in the UTC version of the dates you want to constrain the results to

CRM 2011 Custom page and SQL TimeZone Issue

2 Answers