Convert From Bigint to datetime value

Question

Hi people, please help me with this one, i want to convert a value from Bigint to datetime. For example im reading HISTORY table of teamcity server, on the field *build_start_time_server* i have this value on one record 1283174502729.

How can i convert to datetime value???

Thanks

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 won't work with a number that large. So you do lose a little precision, but it is much simpler to use.