[T-SQL] Converting between DateTime2(7), Time(7) and BigInt in SQL 2012/2014

[Squirrelington]

I ran into a situation where I wanted to get the difference between 2 datetime2(7)s and a time(7). The issue was that subtraction of a datetime2 is not supported.

 

DATEDIFF(ns, 0, cast('2015-01-01 00:00:00.0000000' as datetime2(7))) wouldn't work for converting to nanoseconds because int doesn't support that large of a value and DATEDIFF)BIG doesn't exist until SQL Server 2016 and I didn't want to extract each individual DATEPART the reconstruct the value after getting the differences from each section.

 

So I wrote a few scalar functions for SQL to allow conversion between the 3 formats. They're listed below, hopefully this helps someone.

 

CREATE FUNCTION [BigIntToDateTime2](@ticks bigint, @precision binary(1))
RETURNS DATETIME2(7)
WITH SCHEMABINDING AS
BEGIN
declare @dtTime bigint = @ticks % 864000000000;
declare @dtDays bigint = (@ticks - @dtTime) / 864000000000;
declare @dtTimeBytes binary(5) = CAST(REVERSE(CONVERT(binary(5), @dtTime)) as binary(5))
declare @dtDaysBytes binary(3) = CAST(REVERSE(CONVERT(binary(3), @dtDays)) as binary(3))

return CAST(CAST(@precision + @dtTimeBytes + @dtDaysBytes as binary(9)) as datetime2(7))
END


CREATE FUNCTION [BigIntToTime](@ticks BIGINT, @precision binary(1))
RETURNS TIME(7)
WITH SCHEMABINDING AS
BEGIN
declare @dtTimeBytes binary(5) = CAST(REVERSE(CONVERT(binary(5), @ticks)) as binary(5))

return CAST(CAST(@precision + @dtTimeBytes as binary(6)) as time(7))
END


CREATE FUNCTION [DateTime2ToBigInt](@dt DATETIME2(7))
RETURNS BIGINT
WITH SCHEMABINDING AS
BEGIN
declare @dtBinary binary(9) = CAST(REVERSE(CONVERT(binary(9), @dt)) as binary(9));
declare @dtDateBytes binary(3) = SUBSTRING(@dtBinary, 1, 3);
declare @dtTimeBytes binary(5) = SUBSTRING(@dtBinary, 4, 5);
declare @dtPrecisionByte binary(1) = SUBSTRING(@dtBinary, 9, 1);

return (CONVERT(bigint, @dtDateBytes) * 864000000000) + CONVERT(bigint, @dtTimeBytes)
END


CREATE FUNCTION [DateTime2ToTime](@dt datetime2(7))
RETURNS TIME(7)
WITH SCHEMABINDING AS
BEGIN
declare @dtBinary binary(9) = CONVERT(binary(9), @dt)
declare @dtTimeBytes binary(5) = SUBSTRING(@dtBinary, 2, 5);
declare @dtPrecisionByte binary(1) = SUBSTRING(@dtBinary, 1, 1);

return cast(@dtPrecisionByte + @dtTimeBytes as time(7))
END


CREATE FUNCTION [TimeToDateTime2](@time TIME(7))
RETURNS DATETIME2(7)
WITH SCHEMABINDING AS
BEGIN
return CAST(CAST(@time as binary(9)) as datetime2(7))
END


CREATE FUNCTION [TimeToBigInt](@time TIME(7))
RETURNS BIGINT
WITH SCHEMABINDING AS
BEGIN
declare @timeBytes binary(5) = SUBSTRING(CAST(REVERSE(CAST(@time as BINARY(6))) as binary(6)), 1, 5)

return CAST(@timeBytes as bigint)
END

 

 

 

 

 

Examples:

declare @dt2 datetime2(7) = '2015-01-01 00:04:21.3420544'

declare @dt2bi bigint = (SELECT [dbo].[DateTime2ToBigInt](@dt2))
declare @bidt2 datetime2(7) = (SELECT [dbo].[BigIntToDateTime2](@dt2bi, 7))
declare @dt2t time(7) = (SELECT [dbo].[DateTime2ToTime](@dt2))
declare @tdt2 datetime2(7) = (SELECT [dbo].[TimeToDateTime2](@dt2t))
declare @tbi bigint = (SELECT [dbo].[TimeToBigInt](@dt2t))
declare @bit time(7) = (SELECT [dbo].[BigIntToTime](@tbi, 7))


select @dt2bi as [DateTime2ToBigInt]
select @bidt2 as [BigIntToDateTime2]
select @dt2t as [DateTime2ToTime]
select @tdt2 as [TimeToDateTime2]
select @tbi as [TimeToBigInt]
select @bit as [BigIntToTime]

 

Edited May 4, 2016 by Squirrelington

[Squirrelington]

Forgot to explain my end scenario entirely. I had a sessionstart datetime2(7), sessionend datetime2(7) and an idletime time(7) and I wanted to take (sessionend - sessionstart) - idletime and show it as a time(7) of the activetime.

 

[dbo].[BigIntToTime]([dbo].[DateTime2ToBigInt]([SessionEnd]) - [dbo].[DateTime2ToBigInt]([SessionStart]) - [dbo].[TimeToBigInt]([IdleTime]), 0x07) as [ActiveTime]

 

I eventually wrapped it into a view.

Edited May 4, 2016 by Squirrelington

[Squirrelington]

This is C# code but is related, I used this when I was figuring out what bytes equaled what. It is a DateTime2, Time, Int24 and Int40 structure for C#.

 

    [StructLayout(LayoutKind.Explicit)]
    [Serializable]
    public struct DateTime2
    {
        [FieldOffset()]
        public byte Precision;
        [FieldOffset(1)]
        public Int40 Ticks;
        [FieldOffset(6)]
        public Int24 Days;

        public DateTime2(byte[] data)
        {
            if (data.Length >= 9)
            {
                Precision = data[];
                Ticks = new Int40(data, 1);
                Days = new Int24(data, 6);
            }
            else
                throw new ArgumentException();
        }

        public DateTime ToDateTime()
        {
            return new DateTime(Ticks.Value).AddDays(Days.Value);
        }
    }

    [StructLayout(LayoutKind.Explicit)]
    [Serializable]
    public struct Time
    {
        [FieldOffset()]
        public byte Precision;
        [FieldOffset(1)]
        public Int40 Ticks;

        public Time(byte[] data)
        {
            if (data.Length >= 6)
            {
                Precision = data[];
                Ticks = new Int40(data, 1);
            }
            else
                throw new ArgumentException();
        }

        public DateTime ToDateTime()
        {
            return new DateTime(Ticks.Value);
        }
    }

    [StructLayout(LayoutKind.Explicit)]
    [Serializable]
    public struct Int24
    {
        [FieldOffset()]
        private byte _b0;
        [FieldOffset(1)]
        private byte _b1;
        [FieldOffset(2)]
        private byte _b2;

        public unsafe Int24(byte[] number, int startIndex = )
        {
            if (number.Length < 3)
                throw new ArgumentException();

            _b0 = new byte();
            _b1 = new byte();
            _b2 = new byte();
            fixed (byte* n = number)
            {
                _b0 = *(n + startIndex);
                _b1 = *(n + startIndex + 1);
                _b2 = *(n + startIndex + 2);
            }
        }

        public int Value
        {
            get
            {
                return _b0 |
                    (_b1 << 8) |
                    (_b2 << 16);
            }
        }

        public override string ToString()
        {
            return Value.ToString();
        }
    }

    [StructLayout(LayoutKind.Explicit)]
    [Serializable]
    public struct Int40
    {
        [FieldOffset()]
        private byte _b0;
        [FieldOffset(1)]
        private byte _b1;
        [FieldOffset(2)]
        private byte _b2;
        [FieldOffset(3)]
        private byte _b3;
        [FieldOffset(4)]
        private byte _b4;

        public unsafe Int40(byte[] number, int startIndex = )
        {
            if (number.Length < 5)
                throw new ArgumentException();

            _b0 = new byte();
            _b1 = new byte();
            _b2 = new byte();
            _b3 = new byte();
            _b4 = new byte();
            fixed (byte* n = number)
            {
                _b0 = *(n + startIndex);
                _b1 = *(n + startIndex + 1);
                _b2 = *(n + startIndex + 2);
                _b3 = *(n + startIndex + 3);
                _b4 = *(n + startIndex + 4);
            }
        }

        public long Value
        {
            get
            {
                return _b0 |
                    ((long)_b1 << 8) |
                    ((long)_b2 << 16) |
                    ((long)_b3 << 24) |
                    ((long)_b4 << 32);
            }
        }

        public override string ToString()
        {
            return Value.ToString();
        }
    }