1

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 ?

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

ELSE IF @TimeZone='CET'  
BEGIN
SELECT @CetDate = CASE WHEN @UTCDate <= @DstEnd AND @UTCDate >= @DstStart
    THEN DATEADD(hour, +2, @UTCDate)
    ELSE DATEADD(hour, +1, @UTCDate) END
END
ELSE IF @TimeZone='PHT'  
BEGIN
SELECT @CetDate = CASE WHEN @UTCDate <= @DstEnd AND @UTCDate >= @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
user2171512
  • 531
  • 1
  • 11
  • 28
  • Create a `datetime` from that field and pass to the function? – Kirill Slatin Apr 28 '15 at 07:24
  • what you mean by create datetime from that field ? Convert from int to datetime doesn't work fine – user2171512 Apr 28 '15 at 07:40
  • That's exactly the point. I don't quite understand how you are expect an `int` year to be timezone corrected... Unless you provide some logic how your integers form a valid datatime you can't shift zone. For a year I can think of constructing a _default_ date: `yyyy-01-01 00:00` and then change timezone to it. Actually this will have effect only for negative timezones... – Kirill Slatin Apr 28 '15 at 07:43
  • If you have troubles creating `datetime` in T-SQL from integers, read this [SO post](http://stackoverflow.com/questions/266924/create-a-date-with-t-sql) – Kirill Slatin Apr 28 '15 at 07:52

0 Answers0