Tali B. Tali B. - 4 months ago 38
SQL Question

Convert specific BigInt to DateTime in T-SQL

I have bigInt:

635307578922100000
which I need to convert to
DateTime
.

I've tried few ways to do this:

SELECT
DATEADD(S, CONVERT(bigint,635307578922100000) / 1000, CONVERT(DATETIME, '1-1-1970 00:00:00'))


and:

SELECT
DATEADD(ms, 635307578922100000 / 86400000, (635307578922100000 / 86400000) +25567)


While I found the codes above work with
bigInts
like:
1283174502729
, with my bigInt I get the following error:


Msg 8115 ... Arithmetic overflow error converting wxpression to data
type datetime.


Does anyone have any idea how to solve it?

Answer

Here are 2 calculations that can calculate the bigint to a datetime.

select 
tick.value, 

-- 10000000 * 24 * 60 * 60 = 864000000000
CAST((tick.value - 599266080000000000) / 864000000000 AS datetime) as DateTimeCalc1,


DATEADD(MI, ((tick.value - 599266080000000000) * POWER(10.00000000000,-7) / 60), '1900-01-01') as DateTimeCalc2

from (values 
(convert(bigint,635307578922100000)),
(convert(bigint,599266080000000000))
) tick(value);

Result:

value               DateTimeCalc1           DateTimeCalc2
635307578922100000  2014-03-18 16:44:52.210 2014-03-18 16:44:00.000
599266080000000000  1900-01-01 00:00:00.000 1900-01-01 00:00:00.000

Note that the first is only accurate to the second.
While the second is only accurate to the minute.

Comments