On the plus side, that tweak certainly seems to work. But I'd like to know how we can be certain it will always work. The input datetime is rounded from 08:59:59.999 to 09:00:00.000, The lookup table values are also rounded (when inserted!!) as shown with a simple select:

select * from timezone_offsets where time_zone_id like 'Ritesh/%'

-- returns this:

time_zone_id start_time_gmt end_time_gmt offset

----

Ritesh/Denver 2008-11-02 08:00:00.000 2009-03-08 09:00:00.000 -25200000

Ritesh/Denver 2009-03-08 09:00:00.000 2009-11-01 08:00:00.000 -21600000

Ritesh/Denver 2009-11-01 08:00:00.000 2010-03-14 09:00:00.000 -21600000

So why does the function use the later time range row rather than the earlier? The value '2009-03-08 09:00:00.000' (whether from ms rounding or explicit) matches both the first and second offset entry, so we are in this case depending on the order of retrieval to be determined by the clustered index so that the last value found is the later range. This may work, but it is not considered good practice to depend on the order of data retrieval by any means other than an "order by" clause.

Rather than code "order by" into the function or depend on the clustered index for order, I think I still prefer to simply eliminate the ambiguity caused by the BETWEEN operator as I did in my version of the function. If one decides that depending on the clustered index makes sense from an expediency point of view, clear comments would be pretty much necessary in the code and documentation explaining what's going on.