How do I convert a SQL Server DATETIME value to a Unix timestamp?
Many people have asked how to convert a DATETIME value in SQL Server to a more familiar Unix-style timestamp. Well, the timestamp is merely an offset, in seconds, from Jan 1, 1970, at midnight. Therefore:
Now, there is one caveat with this method. It will only work on dates prior to 2038-01-19 at 3:14:08 AM, where the delta in seconds exceeds the limit of the INT datatype (INT is used as the result of DATEDIFF). If you want your code to handle dates beyond 2038-01-18, and you are running SQL Server 2000, you can write a user-defined function:
The Other Way
Also, some people have asked how to get the datetime value when all they have is a Unix timestamp. Similar to above, only you use DATEADD. Let's say your timestamp value is 1067442089, then to determine what point in time that represents, you would run:
(Of course you could also do this against a column in a table, or a variable, etc.)