My job would be so much easier if everyone on the planet used UTC.

DECLARE @switchhour int
DECLARE @localdate datetime

-- The switch occurs on the second after the
-- first 1:59:59am local time,
-- so times before the first 1:59:59am local time use
-- yesterday's offset and times after the first
-- 1:59:59am local time use today's offset
-- NOTE: This won't work for timezones with
-- half-hour offsets from UTC
SET @switchhour = (@OffsetYesterday / 60) + 2

-- If yesterday and today's offset are the same,
-- adjust from UTC using yesterday's offset
-- Else if the offsets are different, and the
-- Login/Logout datetime is from yesterday,
-- adjust from UTC using yesterday's offset
-- Else if the offsets are different,
-- the Login/Logout datetime is from today,
-- and it is before 2:00am, adjust from UTC using
-- yesterday's offset
-- Else adjust from UTC using today's offset
SET @localdate = CASE
WHEN @OffsetYesterday = @OffsetToday THEN DATEADD(n, -1 * @OffsetYesterday, @UTCDate)
ELSE CASE
WHEN @DateIDYesterday = CONVERT(nvarchar(8), @UTCDate, 112) THEN DATEADD(n, -1 * @OffsetYesterday, @UTCDate)
ELSE CASE
WHEN DATEPART(hh, @UTCDate) < @switchhour THEN DATEADD(n, -1 * @OffsetYesterday, @UTCDate)
ELSE DATEADD(n, -1 * @OffsetToday, @UTCDate)
END
END
END

RETURN @localdate