T SQL Convert Unix Ticks to DateTime

Question

Lets consider this BigInt:

duration = 20732867458

I can convert it to show me the minutes like this:

Select CONVERT(varchar, DATEADD(mi,duration/600000000,0),114) as DurationInHours... `

This gives me 00:34:00:000

I divide by 600million according to this suggestion: Convert UNIX timestamp to .NET DateTime ticks in unmanaged code (without using .NET)

The above approach does not return accurate values for seconds and miliseconds though. The duration is 34,55 minutes if i divide the duration as float by 600 million.

I would expect something like 00:34:30:762

I managed to do in with c# .Net but now I need to do it in TSQL.

check this: http://stackoverflow.com/questions/2904256/how-can-i-convert-bigint-unix-timestamp-to-datetime-in-sql-server — MaxU - stand with Ukraine, Feb 19 '16 at 22:53
@MaxU I already tried this function but for a lot of bigint values in my table I get following error: "Arithmetic overflow error converting expression to data type int" — mJay, Feb 22 '16 at 20:06
I have tried to convert bigint `20732867458` from you example, in datetime on http://www.epochconverter.com/. That gives me `GMT: Sun, 31 Dec 2626 17:50:58 GMT`... Cant see 34 minutes. What I'm doing wrong? — gofr1, Mar 07 '16 at 20:03
[gofr1](http://stackoverflow.com/users/2893376/gofr1), the reason is that the converter at epochconverter.com is based on seconds since the epoch, not ticks since the epoch. As there are 10,000,000 ticks per second, you would need to enter just `2073` (i.e. `20732867458 / 10000000`) to get the same result just to the granularity of seconds — 3N1GM4, Nov 18 '16 at 08:43

3N1GM4 · Answer 1 · 2016-11-02T08:50:02.953

I would break down the overall number of ticks into complete Days, Hours, Minutes, Seconds and Milliseconds, then do a DATEADD for each of those dateparts to build a final datetime value to return.

For example, the UNIX timestamp 14767117605430000 is equivalent to '2016-10-17 13:42:40.543' if there are 10,000,000 ticks per second, so I would calculate this in T-SQL as follows:

    -- ticks per millisecond =       10,000
    -- ticks per second =        10,000,000
    -- ticks per minute =       600,000,000
    -- ticks per hour =      36,000,000,000
    -- ticks per day =      864,000,000,000

    DECLARE @Datetime BIGINT
    SET @Datetime = 14767117605430000

    DECLARE @Days INT
    DECLARE @Hours INT
    DECLARE @Minutes INT
    DECLARE @Seconds INT
    DECLARE @Milliseconds INT

    SET @Days = @Datetime / 864000000000
    SET @Hours = (@Datetime - (CONVERT(BIGINT,@Days) * 864000000000)) / 36000000000
    SET @Minutes = (@Datetime - (CONVERT(BIGINT,@Days) * 864000000000) - (CONVERT(BIGINT,@Hours) * 36000000000))/ 600000000
    SET @Seconds = (@Datetime - (CONVERT(BIGINT,@Days) * 864000000000) - (CONVERT(BIGINT,@Hours) * 36000000000) - (CONVERT(BIGINT,@Minutes) * 600000000)) / 10000000
    SET @Milliseconds = (@Datetime - (CONVERT(BIGINT,@Days) * 864000000000) - (CONVERT(BIGINT,@Hours) * 36000000000) - (CONVERT(BIGINT,@Minutes) * 600000000) - (CONVERT(BIGINT,@Seconds) * 10000000)) / 10000

    SELECT DATEADD(MILLISECOND, @Milliseconds, DATEADD(SECOND, @Seconds, DATEADD(MINUTE, @Minutes, DATEADD(HOUR, @Hours, DATEADD(DAY, @Days, '1970-01-01')))))

You can obviously change the initial value of @Datetime as desired, maybe wrapping this code into a function or stored procedure as required.

This works for any date value up to '9999-12-31 23:59:59.999', so hopefully suits your needs!

T SQL Convert Unix Ticks to DateTime

1 Answers1