EPOCH UTC datetime field- need converted to standard date with local timezone 1

[ljsmith91]

I have an epoch date time stored in a table that is UTC or GMT time. I need to convert the epoch date to standard date and adjust time to local time zone(CST). CST is 6 hours behind UTC or GMT.

If I use DATEADD I can convert epoch UTC to standard datetime in UTC/GMT but how do I also adjust for timezone withing same statement? I need it on the SELECT and WHERE clauses. Example of what I have today.

SELECT
job,
code,
DATEADD(s, epoch_datetime, '1970-01-01') AS END_DATE
FROM backupjob
WHERE (DATEADD(s, epoch_datetime, '1970-01-01') BETWEEN '2013-01-01' AND '2013-01-10')

Thanks for any help or direction on this.

ls

 

[gmmastros]

If you want to use the timeoffset from the server, you can get the hour offset between UTC and local time zone (of the server), like this:

DateAdd(hour, DateDiff(Hour, GetUTCDate(), GetDate()), DATEADD(s, epoch_datetime, '1970-01-01') )

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom