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.

Answer 2

Slightly different approach:

Your scenario:

SELECT dateadd(ms, 1283174502729 / 86400000, (1283174502729 / 86400000) + 25567)
FROM yourtable

Generic code:

SELECT dateadd(ms, yourfield / 86400000, (yourfield / 86400000) + 25567)
FROM yourtable

Output:

August, 30 2010 00:00:14

SQL Fiddle: http://sqlfiddle.com/#!3/c9eb5a/2/0