1

Hi can someone help me with my function. I got this error

Adding a value to a 'datetime2' column caused an overflow.

Here is my function:

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 <= @DstEnd AND @UTCDate >= @DstStart
    THEN DATEADD(hour, +2, @UTCDate)
    ELSE DATEADD(hour, +1, @UTCDate) END

RETURN @CetDate
END

I have in WarehoureMgmt.DimTime tabel dates in format datetime2(7) for example

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

and here is what i want to select

select [DateTime],[dbo].[udf_ConvertfromUTCtoCET] ([DateTime]) 
from WarehouseMgmt.DimTime
Fox
  • 49
  • 1
  • 12
  • Put in some logic that will prevent the code from trying to set any of your variables to a value that will cause an overflow. – Tab Alleman Feb 23 '15 at 14:21
  • possible duplicate of [sql server convert datetime into another timezone?](http://stackoverflow.com/questions/19613638/sql-server-convert-datetime-into-another-timezone) – TobyLL Feb 23 '15 at 14:37
  • it's not a duplicate. I have in db dates in UTC time zone and i want to put them in CET, but also there are summer and winter changes, so i must also convert them correct – Fox Feb 23 '15 at 14:43

1 Answers1

0

The problem is probably a value greater than 9999-12-31 23:00:00.0000000 in your datawarehouse - adding 1 or 2 hours to this will cause the overflow. If you do 

select max ([DateTime]) from WarehouseMgmt.DimTime

you will find the largest value.

Rhys Jones
  • 5,348
  • 1
  • 23
  • 44