With the rows inserted I found even the orignal function with BETWEEN working same. Inserting the rows in the lookup table can give you desired results but I think we can handle it in a better way instead. Check this out.

we need a small change in lookup table

INSERT timezone_offsets(time_zone_id,start_time_gmt,end_time_gmt,offset)

VALUES('Ritesh/Denver','2008-11-02 08:00:00.000','2009-03-08 08:59:59.999',-25200000)

INSERT timezone_offsets(time_zone_id,start_time_gmt,end_time_gmt,offset)

VALUES('Ritesh/Denver','2009-03-08 09:00:00.000','2009-11-01 07:59:59.999',-21600000)

INSERT timezone_offsets(time_zone_id,start_time_gmt,end_time_gmt,offset)

VALUES('Ritesh/Denver','2009-11-01 08:00:00.000','2010-03-14 08:59:59.999',-21600000)

select dbo.get_local_datetime('2009-03-08 08:59:59.555','Ritesh/Denver')

union all

select dbo.get_local_datetime('2009-03-08 08:59:59.999','Ritesh/Denver')

union all

select dbo.get_local_datetime('2009-03-08 08:59:59.998','Ritesh/Denver')

Output

2009-03-08 01:59:59.557

2009-03-08 03:00:00.000

2009-03-08 01:59:59.997

Note the milli difference when you ask for 555 it returns 557 and 997 when you ask for 998

However this should take care of the problem pointed out above. This made me to leave the millis to the application layer.