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 expression to data type datetime.
Does anyone have any idea how to solve it?
Here are some calculations that can calculate the bigint to a datetime.
SELECT
tick.value
-- Subtrack the amount of ticks for 1900-01-01 and divide that number by the ticks in 1 day.
-- Then cast or convert that smaller number to a datetime
-- But only accurate to the second.
-- 864000000000 = (10000000 * 24 * 60 * 60)
, CAST((tick.value - 599266080000000000) / 864000000000 AS datetime) as DateTimeCalc1
-- Subtrack the amount of ticks for 1900-01-01 and divide by the ticks in 1 minute.
-- Then add that smaller number as minutes to 1900-01-01
-- Only accurate to the minute
, DATEADD(MINUTE, ((tick.value - 599266080000000000) / 600000000), CAST('1900-01-01' AS DATETIME)) as DateTimeCalc2
-- Same method as for DateTimeCalc2, but include the milliseconds.
-- Accurate to the millisecond
, DATEADD(MILLISECOND, FLOOR((((tick.value - 599266080000000000)/10000)%60000)), DATEADD(MINUTE, FLOOR((tick.value - 599266080000000000)/600000000), CAST('1900-01-01' AS DATETIME))) as DateTimeCalc3
FROM (values
(convert(bigint,635307578922100000))
,(convert(bigint,599266080000000000))
,(convert(bigint,630823257457000000))
,(convert(bigint,646602048000000000))
) AS tick(value);
Result:
value DateTimeCalc1 DateTimeCalc2 DateTimeCalc3
------------------ ----------------------- ----------------------- -----------------------
635307578922100000 2014-03-18 16:44:52.207 2014-03-18 16:44:00.000 2014-03-18 16:44:52.210
599266080000000000 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000
630823257457000000 2000-01-01 12:15:45.697 2000-01-01 12:15:00.000 2000-01-01 12:15:45.700
646602048000000000 2050-01-01 00:00:00.000 2050-01-01 00:00:00.000 2050-01-01 00:00:00.000
With a bit of tampering those datetimes can be truncated or rounded on the seconds.
SELECT tick.value
-- Truncated
, CAST(CONVERT(varchar, CAST((tick.value - 599266080000000000) / 864000000000 AS datetime),20) AS datetime) as DateTimeTruncated
-- Rounded
, CAST(CAST(CAST((tick.value - 599266080000000000) / 864000000000 AS datetime) as datetime2(0)) AS datetime) as DateTimeRounded
-- For dates between 1981-12-14 and 2118-01-19, one could add seconds to 2050-01-01.
, DATEADD(SECOND, ((tick.value - 646602048000000000) / 10000000), cast('2050-01-01' as datetime)) as DateTimeSecondsAdded
FROM (values
(630823257457000000),
(635307578922100000),
(662380857456770000)
) tick(value);