It can be dangerous to use BETWEEN with time values. Does the function given in this article work for times within the last second of an intended range? I don't think so. For example, consider this snippet of table data:INSERT timezone_offsets(time_zone_id,start_time_gmt,end_time_gmt,offset)

VALUES('America/Chihuahua','2009-04-05 09:00:00','2009-10-25 07:59:59',-21600000)

INSERT timezone_offsets(time_zone_id,start_time_gmt,end_time_gmt,offset)

VALUES('America/Denver','2009-01-01 00:00:00','2009-03-08 08:59:59',-25200000)

INSERT timezone_offsets(time_zone_id,start_time_gmt,end_time_gmt,offset)

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

INSERT timezone_offsets(time_zone_id,start_time_gmt,end_time_gmt,offset)

VALUES('America/Edmonton','2009-01-01 00:00:00','2009-03-08 08:59:59',-25200000)What will the function return when input with this data?get_local_datetime('2009-03-08 08:59:59.500','America/Denver')

A solution to this would NOT be to expand the definition of the ranges to the millisecond level. Due to rounding rules (discussed many times in various posts on SSC), BETWEEN just isn't the way to go. The end of the range is better defined as times before the start of the next range.

INSERT timezone_offsets(time_zone_id,start_time_gmt,end_time_gmt,offset)

VALUES('America/Denver','2009-01-01 00:00:00','2009-03-08 09:00:00',-25200000)

INSERT timezone_offsets(time_zone_id,start_time_gmt,end_time_gmt,offset)

VALUES('America/Denver','2009-03-08 09:00:00','2009-11-01 08:00:00',-21600000)

...and the function altered to use that end time:

create function dbo.get_local_datetime

(@date datetime, @time_zone varchar(25))

returns datetime as

BEGIN

declare @local_time datetime

declare @offset_time int

select @offset_time = offset

from timezone_offsets

where @date >= start_time_gmt

and end_time_gmt > @date

and time_zone_id = @time_zone

set @local_time = dateadd(ms, isnull(@offset_time,0), @date)

return @local_time

END