DateAdd function in MS SQL behaves a little different then what we might want it to. The probability of user getting wrong output because of the milli seconds would be quite less as most of the time shifts occur when the applications are least used. I would suggest the application layer take care of milliseconds rounding.
Consider this
select dbo.get_local_datetime('2009-03-08 08:59:59.000','America/Denver')
union all
select dbo.get_local_datetime('2009-03-08 08:59:59.998','America/Denver')
union all
select dbo.get_local_datetime('2009-03-08 09:00:00.000','America/Denver')
Output is
2009-03-08 01:59:59.000
2009-03-08 08:59:59.997
2009-03-08 03:00:00.000
see something weird on the second line.
Now try John's altered function from above
CREATE FUNCTION dbo.Get_local_datetime2
(@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
Run the same test on get_local_datetime2
select dbo.get_local_datetime2('2009-03-08 08:59:59.000','America/Denver')
union all
select dbo.get_local_datetime2('2009-03-08 08:59:59.998','America/Denver')
union all
select dbo.get_local_datetime2('2009-03-08 09:00:00.000','America/Denver')
Output is
2009-03-08 08:59:59.000
2009-03-08 08:59:59.997
2009-03-08 03:00:00.000
This time it's totally off on line 1 and 2
The problem seems to be with AddDate check the output of following
select dateadd(ms, -21600000, '2009-03-08 08:59:59.000')
union all
select dateadd(ms, -21600000, '2009-03-08 08:59:59.990')
union all
select dateadd(ms, -21600000, '2009-03-08 08:59:59.991')
union all
select dateadd(ms, -21600000, '2009-03-08 08:59:59.992')
union all
select dateadd(ms, -21600000, '2009-03-08 08:59:59.993')
union all
select dateadd(ms, -21600000, '2009-03-08 08:59:59.994')
union all
select dateadd(ms, -21600000, '2009-03-08 08:59:59.995')
union all
select dateadd(ms, -21600000, '2009-03-08 08:59:59.996')
union all
select dateadd(ms, -21600000, '2009-03-08 08:59:59.997')
union all
select dateadd(ms, -21600000, '2009-03-08 08:59:59.998')
union all
select dateadd(ms, -21600000, '2009-03-08 08:59:59.999')
The output is
2009-03-08 02:59:59.000
2009-03-08 02:59:59.990
2009-03-08 02:59:59.990
2009-03-08 02:59:59.993
2009-03-08 02:59:59.993
2009-03-08 02:59:59.993
2009-03-08 02:59:59.997
2009-03-08 02:59:59.997
2009-03-08 02:59:59.997
2009-03-08 02:59:59.997
2009-03-08 03:00:00.000
Undoubtedly there is always a chance for improvements. And thanks much for you suggestions.