sql server convert unix timestamp to date
One of the HelpSpot trial users is trying to do some reporting directly from the database and running into a problem with HelpSpot's date storage. For simplicity HelpSpot stores all dates as UNIX timestamps. This makes it easy to keep things cross platform on the DB front, because it's simply stored as an integer. It's also ideal since PHP basically requires this format to use the date functions.
The issue is that Microsoft SQL Server doesn't understand timestamps in this format so I thought I'd post the solution here in case anyone needs this. It's really simple, all you need to do is use SQL Servers dateadd() function to add the number of seconds to the UNIX timestamp starting point of January 1st, 1970. So simply:
SELECT dateadd(ss, {UNIX_timestamp}, '19700101')
or in a query:
SELECT xRequest, dtGMTOpened, dateadd(ss,dtGMTOpened,'19700101') AS opendate FROM HS_Request