Agreed there are questions which only MS SQL can answer. That is the reason I left the millis to Application level.
The max we can do to make it most nearest to perfect is set the millis to 997. Here is what happens
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.993',-25200000)
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.994',-25200000)
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.995',-25200000)
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.996',-25200000)
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.997',-25200000)
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.998',-25200000)
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 * from timezone_offsets where time_zone_id like 'Ritesh/%' and end_time_gmt = '2009-03-08 08:59:59.994'
union all
select * from timezone_offsets where time_zone_id like 'Ritesh/%' and end_time_gmt = '2009-03-08 08:59:59.995'
While clustered index and millis set to 997 take care of the problem and possibility of someone hitting those 2 milli 998 and 999 and getting wrong output is like Lightning strikes twice at the same place. I would rather ignore the millis sent to the function and just return output as per floor second passed by in this case 59th second.
If the issue makes much of a concern to application I would recommend to handle this slightest of possibility at application layer.