I have input data with date in epoch format (seconds since the
beginning...),
I am trying to convert it to visual representation and the local time
zone is playing tricks on me.

declare @dt as datetime;
declare @ss as int;

set @ss = 1287783600 -- my input for 2010-10-22 14:40
set @dt = '2010-10-22 14:40:00.000'

select @ss as [Epoch], DATEADD(s, @ss, '19700101 00:00:00') as [CALC],
@dt as [ACTUAL]

Output:
Epoch CALC ACTUAL
----------- ----------------------- -----------------------
1287783600 2010-10-22 21:40:00.000 2010-10-22 14:40:00.000

There are 7 hours difference between Actual date and calculated, which
is our local time zone offset now.

How do I convert it so calculated date is equal to my input?
Running on SQL2005,
thanks.

migurus (migurus (AT) yahoo (DOT) com) writes:
SQL 2005 has notion of of time zones, so you have roll your own. Datetime
values since the epoch is usually in UTC, so the calculated value is
correct. If you want to see the time in your local time zone, you need
to use dateadd to add or subtract your offset from UTC.

This is fairly trivial if you live in a place where they do not use
daylight savings time. But if you live in a place where you switch
from and to DST twice a year, you need to take that in account. A tip
is that if you do this in SQLCLR you can rely on the routines provided
by windows. Although, if you want to compensate in changes of DST rules
over the years, that can also be a difficult thing.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx