Convert specific BigInt to DateTime in T-SQL

I have bigInt: 635307578922100000 which I need to convert to DateTime.

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 like: 1283174502729, with my bigInt I get the following error:

bigInts

1283174502729

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 8:12

It looks like these are ticks. Possible duplicate of Convert .NET Ticks to SQL Server DateTime - using the answer here gives 2016-07-26 09:12:00.000
– Bridge
Jul 26 '16 at 8:13

2016-07-26 09:12:00.000

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 8:17

int

what is that date? Even If it is in microseconds, it is still 20,000 years+
– Cato
Jul 26 '16 at 8:43

@Bridge I could never think of it myself. Thank you so much!
– Tali B.
Jul 26 '16 at 9:21

2 Answers
2

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


Perfect solution. Thanks.
– Tali B.
Jul 26 '16 at 9:30

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.

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.