Tali B. - 6 months ago 73

SQL Question

I have bigInt:

`635307578922100000`

`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`

`1283174502729`

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.