database - GETUTCDATE() and Time Zone (SQL Server)

Question

I want to add my time zone with the function GETUTCDATE() in SQL Server. I searched several times, but did not found any relevant solution. Thanks in advance.

Answer 1

only for sql 2016, it takes into account daylight savings.

CREATE FUNCTION GetBelgiumTime 
(
)
RETURNS datetime2
AS BEGIN  
       declare @dateoffset datetimeoffset
       SET  @dateoffset = convert(VARCHAR(2000),(SELECT GETUTCDATE() AT TIME ZONE  'Central European Standard Time'),126 )


       declare @date datetime2
       set @date = convert(datetime2, LEFT(@dateoffset,28),126)

    set @date = DATEADD(HOUR, convert(int,LEFT(RIGHT(@dateoffset,5), 2)), @date) 
       RETURN @date
END

select  dbo.GetBelgiumTime() as BelgiumDateAndTime