This is a fast way to convert a bigint value ( int is not big enough) representing a Unix UTC timestamp to datetime. It is a quite common problem . This solution is very fast and suitable for processing large number of rows, because uses pure TSQL and no UDF .
It is necessary to create a dimension DIM_DAY, containing also the information if a given day is summer or winter time .
Then, there is a clear example of usage .
Note that Offset is 2009 because from 1970 would overflow datatype int and dateadd accepts only int.
Slovakia has GMT+1 , in other time zone just change the values 7200 and 3600 in the select .
This method is fast, since uses TSQL hash join , there is only small disavatage that changing form summer to winter time is assumed to be at 00:00 , while in reality is at 02:00 am, but ofr business purpose in general it makes small difference.
If the timestamp is espressed in Hex format, you can convert it easilyt into numeric as described in http://www.sqlservercentral.com/Contributions/76413
