Wednesday, July 24, 2013

Get the Central European Time in SQL Server

Getting the Central European Time (http://en.wikipedia.org/wiki/Central_European_Time) is requirement some of the time when we build the Product for Clients spreaded in European Countries.

Here it the function I created to which return the same and it manage the day light saving internally.


CREATE FUNCTION ufs_Datetime_GetCETTime
RETURNS DATETIME
AS
BEGIN
 DECLARE @DstStart DATETIME
 DECLARE @DstEnd DATETIME
 DECLARE @CETDateTime DATETIME
 DECLARE @UTCDateTime DATETIME

 SET @UTCDateTime = GETUTCDATE()

 SELECT @DstStart = DATEADD(hour, 1, DATEADD(day, DATEDIFF(day, 0, '31/Mar' + CAST(YEAR(@UTCDateTime) AS VARCHAR)) - (DATEDIFF(day, 6, '31/Mar' + CAST(YEAR(@UTCDateTime) AS VARCHAR)) % 7), 0))
  ,@DstEnd = DATEADD(hour, 1, DATEADD(day, DATEDIFF(day, 0, '31/Oct' + CAST(YEAR(@UTCDateTime) AS VARCHAR)) - (DATEDIFF(day, 6, '31/Oct' + CAST(YEAR(@UTCDateTime) AS VARCHAR)) % 7), 0))

 SELECT @CETDateTime = CASE 
   WHEN @UTCD ateTime & lt;= @DstEnd
    AND @UTCDateTime & gt;= @DstStart
    THEN DATEADD(hour, + 2, @UTCDateTime)
   ELSE DATEADD(hour, + 1, @UTCDateTime)
   END

 RETURN @CETDateTime
END


Comments awaited.. Thanks, Prajesh

No comments: