Posted by laurenq uantrell on 10/09/20 11:33

On the client side, it's pretty simple since date() remains in the
chosen format as long as I never format it, so Nov.29, 2005 remains
29/11/2005 or 11/29/2005 depending on the regional format - the
DATEDIFF on the client side will always calculate correctly. What I
need to do is to translate the client date to UTC/GMT and pass that as
an integer to the server to handle the time zone differences as I have
shown below:

@GMTOffset int, /* client GMT offset in minutes - example NYC = 300
*/
@StartDays int, /* Number of days from today's date */
@EndDays int /* Number of days from today's date */

AS

DECLARE @ClientUTCDIFF int, @ServerUTCDIFF int, @ServerStartDate
datetime, @ServerEndDate datetime

/* determine the differnence between user client GMT offset and server
GMT Offset: */
SELECT @ServerUTCDIFF = DATEDIFF(MINUTE, GETDATE(), GETUTCDATE())
SELECT @ClientUTCDIFF = @GMTOffset - @ServerUTCDIFF

/* set server start date/time to GMT hour at midnight user's time today
- example NYC = 5am GMT*/
SELECT @ServerStartDate = DATEADD(MINUTE,@ServerUTCDIFF +
@ClientUTCDIFF,CAST(CONVERT(nvarchar(10),GETDATE(),101) AS Datetime))

/* set server end date/time to GMT hour at 11:59 pm user's time today -
example NYC = 4:49 am GMT*/
SELECT @ServerEndDate = DATEADD(MINUTE,1439,@ServerStartDate)

/* adjust the server date/time to the filter date range requested by
the user: */
SELECT @ServerStartDate = DATEADD(DAY,@StartDays, @ServerStartDate)
SELECT @ServerEndDate = DATEADD(DAY,@EndDays,@ServerEndDate)

/* display the date parameters that will be used: */

SELECT
@ServerStartDate AS ServerStartDate,
@ServerEndDate AS ServerEndDate

[Back to original message]