/*
*    Converts the provided datetimeoffset to the supplied time zone.
*    Use the GetBestUserTimeZoneName stored proc to determine
*    the best time zone for the current user.  In Embarcadero C++
*    code, the @inBestTimeZone value is looked up and stored in the
*    ApplicationSession object the first time it is needed.
*
*    If no time zone is supplied, returns the @inDateTimeOffset as is,
*    which means that it will be displayed in the time zone that the
*    datetimeoffest was created in.
*/
create function [dbo].[ConvertDateTimeOffsetToBestTimeZone]
(
    @inDateTimeOffset datetimeoffset,
    @inBestTimeZone varchar(64)
)
returns datetime as
begin
    if( isnull( @inBestTimeZone, '' ) = '' )
    begin
        return @inDateTimeOffset
    end
    declare @theUtcDateTime2 datetime2 = convert( datetime2, @inDateTimeOffset, 1 )
    declare @theConvertedTime datetime = Tzdb.ConvertZone( @theUtcDateTime2, 'Etc/UTC', @inBestTimeZone, 1, 1 )
    return isnull( @theConvertedTime, @inDateTimeOffset )
end
GO
GRANT EXECUTE ON  [dbo].[ConvertDateTimeOffsetToBestTimeZone] TO [MssExec]
GO