T-SQL Daylight Saving lookup table - poorly performing table-valued function
If Tzid and DT_WhenSwitch define a unique row I recommend clustering the dbo.DSTLookup table by those two columns. You can make those columns the primary key if you want or you can just make them be the clustered index.
CREATE TABLE dbo.DSTLookup 
(
     [Id] int, 
     [Tzid] int, 
     [DT_WhenSwitch] datetime, 
     [DSTOffSetSeconds] int, 
     [GMTOffSetSeconds] int 
);

CREATE CLUSTERED INDEX CI_DSTLookup ON dbo.DSTLookup ([Tzid], [DT_WhenSwitch]); -- new

INSERT INTO dbo.DSTLookup
VALUES (29, 2, N'2014-03-30T01:00:00', 3600, 0), 
       (30, 2, N'2014-10-26T02:00:00', 0, 0), 
       (31, 2, N'2015-03-29T01:00:00', 3600, 0), 
       (32, 2, N'2015-10-25T02:00:00', 0, 0), 
       (33, 2, N'2016-03-27T01:00:00', 3600, 0), 
       (34, 2, N'2016-10-30T02:00:00', 0, 0), 
       (35, 2, N'2017-03-26T01:00:00', 3600, 0), 
       (36, 2, N'2017-10-29T02:00:00', 0, 0), 
       (37, 2, N'2018-03-25T01:00:00', 3600, 0), 
       (38, 2, N'2018-10-28T02:00:00', 0, 0);

The reason for doing that is it will allow very fast individual row lookups. For both queries against the table you want to filter on [Tzid] and to find the first [DT_WhenSwitch] value in descending order. With the right clustered index getting that row can be a single clustered index seek.
To get that the plan I want I'm going to simplify the TVF a bit with the APPLY and TOP operators. I also want to make it very obvious to the optimizer that I get back just one row every time. Here's one implementation:
CREATE FUNCTION dbo.FN_GetLocalTime_FromUTC_BasedOnTZId 
     (@StartDateTime DATETIME, @EndDateTime DATETIME, @Tzid INT)
/*=========================================================================
*   2017-03-27
*   Returns local time from UTC time based on timeZoneId
*
==========================================================================*/
RETURNS TABLE 
WITH SCHEMABINDING
AS
RETURN
(
        SELECT
               DATEADD(SECOND, (COALESCE(S.s_DST_OffSet, 0) + COALESCE(S.s_GMT_OffSet, 0)), @StartDateTime) 'LocalStartDateTime'
             , DATEADD(SECOND, (COALESCE(E.e_DST_OffSet, 0) + COALESCE(E.e_GMT_OffSet, 0)), @EndDateTime)   'LocalEndDateTime'
        FROM (SELECT 1 t) t
        OUTER APPLY (
            SELECT TOP 1 
                D.DSTOffSetSeconds 's_DST_OffSet',
                D.GMTOffSetSeconds 's_GMT_OffSet'
            FROM dbo.DSTLookup D
            WHERE D.DT_WhenSwitch &lt;= @StartDateTime AND D.Tzid = @Tzid
            ORDER BY D.DT_WhenSwitch DESC
        ) s
        OUTER APPLY (
            SELECT TOP 1  
                 D.DSTOffSetSeconds 'e_DST_OffSet',
                 D.GMTOffSetSeconds 'e_GMT_OffSet'
             FROM dbo.DSTLookup D
             WHERE D.DT_WhenSwitch &lt;= @EndDateTime AND D.Tzid = @Tzid
             ORDER BY D.DT_WhenSwitch DESC
        ) e
);

Here is the query plan for your example query in the question:

As expected we only do two seeks against the clustered index:

(1 row(s) affected)
Table 'DSTLookup'. Scan count 2, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms,  elapsed time = 1 ms.

I wasn't able to test against SQL Server 2008, but I think that syntax will work on that platform. db fiddle for SQL Server 2014.