Search code examples
sql-servert-sqltimezonesql-function

How to use a User Defined Function to iterate through column values

I have a function which converts UTC time into local time based on a certain office location. The two parameters for the function are the UTC as datetime2 data type and Office as int data type.

SELECT [fn].[ConvertFromUTC]('2021-03-14 07:00:00', 5740)

Result: 2021-03-14 03:00:00

Here is the a table that I am wanting to convert all the UTC times to their local times based on the Support Site location (office).enter image description here

What would be the best way to go about doing this? I tried using something like this but am not sure how to iterate through each support site and UTC time. Suggestions? Would a Cursor be ideal in this scenario?

DECLARE @meh nvarchar(50)
DECLARE @x datetime2
DECLARE @y int

Set @x = '2021-03-14 07:00:00'
Set @y = 20608
        
EXEC    @meh = fn.ConvertFromUTC
            @DateTime = @x, 
            @Office = @y

SELECT  @meh

Here is the code for the function.

ALTER Function [fn].[ConvertFromUTC]
/*This function converts a DateTime from UTC to local time at each office.*/
    
(
@DateTime DATETIME2(0),
@Office int
)

RETURNS DATETIME2(0)
AS

BEGIN
    DECLARE @TimeZone nvarchar(50)
    DECLARE @Result DATETIME2(0)
    
    IF @Office IN ('20608','5740')
        BEGIN
            SET @TimeZone = 'US Eastern Standard Time'
            SET @Result = @DateTime at time zone 'UTC' at time zone @TimeZone
        END 
    ELSE IF @Office = '597'
        BEGIN
            SET @TimeZone = 'W. Europe Standard Time'
            SET @Result = @DateTime at time zone 'UTC' at time zone @TimeZone
            SET @Result = DATEADD(HOUR,-1,@Result) /* 'AT TIME ZONE' functionality for Europe uses the wrong offset. This corrects it.*/
        END 
    ELSE IF @Office = '6179' /*Not using 'AT TIME ZONE' functionality because it doesn't recognize that Australia observes Daylight Savings Time*/
        BEGIN   
            /*DateTime is the end and beginning of Sydney daylight savings time in UTC. 
            Ends first Sunday in April at 2:00:00 and begins first Sunday in October at 3:00:00
            In UTC, Ends first Saturday in April at 16:00:00 and begins first Saturday in October 16:00:00*/
            IF @DateTime >= DATEADD(dd, (5-(DATEDIFF(dd,0,DATEADD(mm,(YEAR(@DateTime)-1900) * 12 + 3,0))%7)),DATEADD(mm,(YEAR(@DateTime)-1900) * 12 + 3,0))+'16:00:00'
            AND @DateTime < DATEADD(dd,0 + (5-(DATEDIFF(dd,0,DATEADD(mm,(YEAR(@DateTime)-1900) * 12 + 9,0))%7)),DATEADD(mm,(YEAR(@DateTime)-1900) * 12 + 9,0))+'16:00:00'
                BEGIN
                    SET @Result = DATEADD(hour,10,@DateTime)    
                END
            ELSE
                BEGIN
                    SET @Result = DATEADD(hour,11,@DateTime)    
                END
        END 

    RETURN @Result

END
Solution

  • Firstly, what you have currently is termed a scalar User Defined Function (UDF).

    You can use it quite simply like this:

    SELECT fn.ConvertFromUTC(t.YourDate, 5740)
FROM YourTable t;

    Scalar UDFs are slow for various reasons and should be avoided, and although most are helped by SQL Server 2019's UDF inlining, it is normally better to rewrite this as an inline Table Valued Function. This returns a rowset, and is a bit like a parameterized view.

    To make it inline, it must be a single RETURN (SELECT statement

    CREATE OR ALTER Function [fn].[ConvertFromUTC]
/*This function converts a DateTime from UTC to local time at each office.*/
(
  @DateTime DATETIME2(0),
  @Office int
)

RETURNS TABLE
AS RETURN
(
    SELECT Result = CASE 
      WHEN @Office IN ('20608', '5740')
        THEN @DateTime AT TIME ZONE 'UTC' AT TIME ZONE 'US Eastern Standard Time'
      WHEN @Office = '597'
        THEN DATEADD(HOUR, -1, @DateTime AT TIME ZONE 'UTC' AT TIME ZONE 'W. Europe Standard Time')
      WHEN @Office = '6179'
        THEN 

            /*DateTime is the end and beginning of Sydney daylight savings time in UTC. 
            Ends first Sunday in April at 2:00:00 and begins first Sunday in October at 3:00:00
            In UTC, Ends first Saturday in April at 16:00:00 and begins first Saturday in October 16:00:00*/

            CASE WHEN @DateTime >= DATEADD(dd, (5-(DATEDIFF(dd, 0, DATEADD(mm, (YEAR(@DateTime) - 1900) * 12 + 3, 0)) %7)), DATEADD(mm,(YEAR(@DateTime) - 1900) * 12 + 3, 0)) + '16:00:00'
                AND @DateTime < DATEADD(dd, 0 + (5 - (DATEDIFF(dd, 0, DATEADD(mm, (YEAR(@DateTime)-1900) * 12 + 9, 0)) % 7)), DATEADD(mm,(YEAR(@DateTime) - 1900) * 12 + 9, 0)) + '16:00:00'
                THEN DATEADD(hour,10,@DateTime)    
                ELSE DATEADD(hour,11,@DateTime)    
            END
      END 
);

GO

    You can use it like this:

    SELECT utc.Result
FROM YourTable t
CROSS APPLY fn.ConvertFromUTC(t.YourDate, 5740) utc;

-- Because it's only one value you can also do this

SELECT
  (SELECT utc.Result FROM fn.ConvertFromUTC(t.YourDate, 5740))
FROM YourTable t;

    I must say, I take issue with the original writer of this function, who clearly knows about AT TIME ZONE, but thinks it doesn't work properly.

                SET @TimeZone = 'W. Europe Standard Time'
            SET @Result = @DateTime at time zone 'UTC' at time zone @TimeZone
            SET @Result = DATEADD(HOUR,-1,@Result) /* 'AT TIME ZONE' functionality for Europe uses the wrong offset. This corrects it.*/

    Europe is not monolithic, presumably the correct time zone should have been 'GMT Standard Time'.

        ELSE IF @Office = '6179' /*Not using 'AT TIME ZONE' functionality because it doesn't recognize that Australia observes Daylight Savings Time*/
        BEGIN   
            /*DateTime is the end and beginning of Sydney daylight savings time in UTC.

    Again, Australia is not one time zone, and I suspect that E. Australia Standard Time was used instead of 'AUS Eastern Standard Time'.

    You can see all the available time zones on the server with select * from sys.time_zone_info, you can also add more time zones via Windows.

    Another thing is that this function is supposed to converts a DateTime from UTC to local time, but using AT TIME ZONE twice is used only when converting from one time zone to another, it should only be done once if the time is already in 'UTC'.

    One further point: I suggest you actually store the correct time zone within the table you are querying, then you can pass through the time zone instead of @Office and avoid a bunch of CASE expressions.