title: Convert from UTC to CET function in SQL
body: <p>I have this function for converting UTC to CET,EET and it's working fine with datetime2 type, but how to make this function working also with int,bigint type,because i will have also [CalendarYear] int field in my table ?</p>
  
  <pre><code>CREATE FUNCTION [dbo].[udf_ConvertfromUTCtoCET] (@UTCDate AS DATETIME2(7),@TimeZone AS nvarchar(5))
  RETURNS DATETIME2(7)
  AS
  BEGIN
  
  DECLARE @DstStart datetime2(7)
  DECLARE @DstEnd datetime2(7)
  DECLARE @CetDate datetime2(7)
  
  SELECT @DstStart = DATEADD(hour, 1,DATEADD(day, DATEDIFF(day, 0, '31/Mar' + CAST(YEAR(@UTCDate) AS varchar)) - 
          (DATEDIFF(day, 6, '31/Mar' + CAST(YEAR(@UTCDate) AS varchar)) % 7), 0)),
      @DstEnd = DATEADD(hour, 1,DATEADD(day, DATEDIFF(day, 0, '31/Oct' + CAST(YEAR(@UTCDate) AS varchar)) - 
          (DATEDIFF(day, 6, '31/Oct' + CAST(YEAR(@UTCDate) AS varchar)) % 7), 0))
  
  
  IF @TimeZone='EET'  
  BEGIN  
  SELECT @CetDate = CASE WHEN @UTCDate &lt;= @DstEnd AND @UTCDate &gt;= @DstStart
      THEN DATEADD(hour, +3, @UTCDate)
      ELSE DATEADD(hour, +2, @UTCDate) END
  END
  
  ELSE IF @TimeZone='CET'  
  BEGIN
  SELECT @CetDate = CASE WHEN @UTCDate &lt;= @DstEnd AND @UTCDate &gt;= @DstStart
      THEN DATEADD(hour, +2, @UTCDate)
      ELSE DATEADD(hour, +1, @UTCDate) END
  END
  ELSE IF @TimeZone='PHT'  
  BEGIN
  SELECT @CetDate = CASE WHEN @UTCDate &lt;= @DstEnd AND @UTCDate &gt;= @DstStart
      THEN DATEADD(hour, +9, @UTCDate)
      ELSE DATEADD(hour, +7, @UTCDate) END
  END
  
  RETURN @CetDate
  END
  
  
  select [DateTime] AS UTC,[dbo].[udf_ConvertfromUTCtoCET] ([DateTime],'CET') AS CET,[dbo].[udf_ConvertfromUTCtoCET] ([DateTime],'EET') AS EET from WarehouseMgmt.DimTime
  </code></pre>