Daylight savings time in Sql Server

Question

We're using an old application that stores dates in C / Unix format. C time is basically the number of seconds since Jan 1st, 1970. The dates are stored as an integer in a SQL Server database. I am writing a view for a report that uses these dates.

So far, I'm converting from the UNIX time to a native datetime with:

DateAdd(s,3600+unix_time,'1/1/1970')

The 3600 is to convert from UTC to our local GMT+1 timezone. This is accurate in the winter, but in the summer it's one hour off due to daylight savings time.

Is there a built-in way to convert from UTC to local time in SQL Server?

Answer 1

Instead of 3600, you'll want to do DateDiff(s, getutcdate(), getdate())+unix_time, which will give you the correct offset from the UTC time.

Cheers,
Eric