Convert bigint to datetime

user_430672 0
Viewed 112284

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?

teamcity tsql sql-server sql
9 Answers

select Cast(Cast(19980324 as nvarchar) as Datetime)

user_32753840

If you want precision in milliseconds to be maintained then you could do as follows. Works on SQL server 2016

SELECT dateadd(ms, ((CONVERT(bigint, build_start_time_server)%1000)), 
       dateadd(ss, ((CONVERT(bigint, build_start_time_server)/1000)%60), 
       dateadd(mi, ((CONVERT(bigint, build_start_time_server)/1000)/60), '1970-01-01'))) FROM yourtable

The answer I got was

Monday, August 30, 2010 1:21 PM
user_40913160

To convert bigint to datetime/unixtime, you must divide these values by 1000000 (10e6) before casting to a timestamp.

SELECT
     CAST( bigIntTime_column / 1000000 AS timestamp) example_date
FROM example_table

Simple and easy solution which won't require any added library or function to be imported

user_142135540
user_91886610
Related