sql - Convert bigint to datetime

Question

I want to convert a value from bigint to datetime.

For example, I'm reading the HISTORY table of teamcity server. On the field build_start_time_server, I have this value on one record 1283174502729.

How can I convert it to a datetime value?

Answer 1

Does this work for you? It returns 30-8-2010 13:21:42 at the moment on SQL Server 2005:

select dateadd(s, convert(bigint, 1283174502729) / 1000, convert(datetime, '1-1-1970 00:00:00'))

I've divided by 1000 because the dateadd function won't work with a number that large. So you do lose a little precision, but it is much simpler to use.