How can I convert bigint (UNIX timestamp) to datetime in SQL Server?

by

This worked for me:

Select
    dateadd(S, [unixtime], '1970-01-01')
From [Table]

In case any one wonders why 1970-01-01, This is called Epoch time.

Below is a quote from Wikipedia:

The number of seconds that have elapsed since 00:00:00 Coordinated Universal Time (UTC), Thursday, 1 January 1970,[1][note 1] not counting leap seconds.

The Year 2038 Problem

Furthermore the DataAdd function takes an int for the seconds to add. So if you are trying to add more than 2147483647 seconds you’ll get an arithmetic overflow error. To resolve this issue you can break the addition into two calls to DateAdd one for the years and one for the remaining seconds.

Declare @t as bigint = 4147483645

Select (@t / @oneyear) -- Years to add
Select (@t % @oneyear) -- Remaining seconds to add

-- Get Date given a timestamp @t
Declare @oneyear as int = 31622400
Select DateAdd(SECOND, @t % @oneyear, DateAdd(YEAR, @t / @oneyear, '1970-01-01'))

This will let you convert a timestamp that represents a year greater than 2038.

More Related Contents:

  1. Convert text to system date format [duplicate]
  2. How to return only the Date from a SQL Server DateTime datatype
  3. Best approach to remove time part of datetime in SQL Server
  4. DateTime2 vs DateTime in SQL Server
  5. SQL query to select dates between two dates
  6. How can I select the first day of a month in SQL?
  7. How to compare datetime with only date in SQL Server
  8. Getting Dates between a range of dates
  9. SQL where clause odd behavior [closed]
  10. How do I escape a single quote in SQL Server?
  11. Simple way to transpose columns and rows in SQL?
  12. Only inserting a row if it’s not already there
  13. Unpivot with column name
  14. Selecting data from two different servers in SQL Server
  15. Maximum size for a SQL Server Query? IN clause? Is there a Better Approach [duplicate]
  16. Using the result of an expression (e.g. Function call) in a stored procedure parameter list?
  17. When no ‘Order by’ is specified, what order does a query choose for your record set?
  18. How do I create a step in my SQL Server Agent Job which will run my SSIS package?
  19. How do I create a foreign key in SQL Server?
  20. How do I group on continuous ranges
  21. join comma delimited data column
  22. How do I query for all dates greater than a certain date in SQL Server?
  23. Need to list all triggers in SQL Server database with table name and table’s schema
  24. Is it possible to perform multiple updates with a single UPDATE SQL statement?
  25. Incorrect syntax near the keyword ‘with’…previous statement must be terminated with a semicolon
  26. Best equivalent for IsInteger in SQL Server
  27. Syntax of for-loop in SQL Server
  28. SQL – Call Stored Procedure for each record
  29. Why is RAND() not producing random numbers?
  30. Should every SQL Server foreign key have a matching index? [closed]

Leave a Comment