Convert specific BigInt to DateTime in T-SQL

Question

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?

What would be your expected result for the given input '635307578922100000' — StackUser, Jul 26 '16 at 08:12
It looks like these are ticks. Possible duplicate of [Convert .NET Ticks to SQL Server DateTime](http://stackoverflow.com/questions/2313236/convert-net-ticks-to-sql-server-datetime) - using the answer here gives `2016-07-26 09:12:00.000` — Bridge, Jul 26 '16 at 08:13
You need to convert this value to number of hours since 01.01.1900 00:00:00, it's the maximum datepart precision which fits `int `. Then add ms left. — Serg, Jul 26 '16 at 08:17
what is that date? Even If it is in microseconds, it is still 20,000 years+ — Cato, Jul 26 '16 at 08:43
@Bridge I could never think of it myself. Thank you so much! — Tali B., Jul 26 '16 at 09:21

Paweł Dyl · Answer 1 · 2016-07-26T08:59:21.793

I think it's in ticks (yields 2014-03-18 16:44:52.210). Here is solution:

SELECT DATEADD(
    MILLISECOND,
    FLOOR(((635307578922100000-599266080000000000)%(10000000*60))/10000),
    DATEADD(
        MINUTE,
        FLOOR((635307578922100000-599266080000000000)/(10000000*60)),
        '01-01-1900'))

This magic value 599266080000000000 is number of ticks between 0001-01-01 and 1900-01-01 calculated in PowerShell as follows:

([DateTime]::Parse('1900-01-01')-[DateTime]::MinValue).Ticks

Conversions are required, because DATEADD won't work with bigint (requires int). SQL Server DateTime is also limited to 1 January 1753.

LukStorms · Accepted Answer · 2018-06-29T11:15:16.417

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);

Convert specific BigInt to DateTime in T-SQL

2 Answers2

Linked