title: Function for converting UTC to CET in sql server
body: <p>Hi can someone help me with my function. I got this error</p>
  
  <blockquote>
    <p>Adding a value to a 'datetime2' column caused an overflow.</p>
  </blockquote>
  
  <p>Here is my function:</p>
  
  <pre><code>CREATE FUNCTION [dbo].[udf_ConvertfromUTCtoCET] (@UTCDate AS DATETIME2(7))
  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))
  
  
  
  SELECT @CetDate = CASE WHEN @UTCDate &lt;= @DstEnd AND @UTCDate &gt;= @DstStart
      THEN DATEADD(hour, +2, @UTCDate)
      ELSE DATEADD(hour, +1, @UTCDate) END
  
  RETURN @CetDate
  END
  </code></pre>
  
  <p>I have in WarehoureMgmt.DimTime tabel dates in format datetime2(7) for example</p>
  
  <pre><code>9999-12-31 00:00:00.0000000
  0001-01-01 00:00:00.0000000
  2012-01-01 00:00:00.0000000
  2012-01-01 01:00:00.0000000
  2012-01-01 02:00:00.0000000
  </code></pre>
  
  <p>and here is what i want to select</p>
  
  <pre><code>select [DateTime],[dbo].[udf_ConvertfromUTCtoCET] ([DateTime]) 
  from WarehouseMgmt.DimTime
  </code></pre>