Question

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 by Squirrelington

2 answers to this question

Recommended Posts

  • 0

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 by Squirrelington
  • 0

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

 

This topic is now closed to further replies.
  • Posts

    • Well to finalise the thread, I bought the addin card as recommended but had teething problems trying to connect it to my eGPU. After much research, I found that rated cabling is a thing and after connecting a rated thunderbolt 4 cable to the new USB addin card I started troubleshooting. A few BIOS tweaks and updates here and there and everything is now connected and working. New eGPU is being picked up by Thunderbolt Control Center Thank you to anyone that stopped and read the thread especially to the tech resources that stopped to post and help. It was of massive help being able to reach out to the community as always. I just hope I haven't wasted too much of the community time trying to diagnose the problem. Thanks for your help again. 😊 Kind regards
    • they keep making "changes" to stuff that isn't broke if I was them I'd just stop making changes to classic and focus on fixing the other mess
    • Every Xbox is a slimmed-down and modified Windows. Xbox 1 (2002) was a Windows NT Xbox 360 Windows 8 Xbox One Windows 10 Xbox SX Windows 11 all with versions with specific services just for games. The Windows that will come on laptops will be a slimmed-down Windows 11 Home focused on games.
    • The problem was that people who have played Extraction Shooters said there were too many missing features. Like Proximity Chat ...because of "toxicity". Lack of any story development even actually outlined. "Too watered down" for regular Extraction Shooter Enjoyers. Stupid amount of Aim Assistance on MnK. Questionable outdoor map design (how can something so "colorful" be so effing dull). Not to mention people still ###### they seemed to use the Marathon name and added in the alien/npcs after the fact, stappled on some other game concept they were working on. Like Arkane Studios was forced to use Prey as a title for their own original IP, by Bethesda to sucker in people for name recognition and ownership over the Prey IP. Which backfired with lower sales, due to the people realizing not a sequel or reboot, and the people who didn't like the original Prey.
  • Recent Achievements

    • Explorer
      Jdoe25 went up a rank
      Explorer
    • First Post
      Ian_ earned a badge
      First Post
    • Explorer
      JaviAl went up a rank
      Explorer
    • Reacting Well
      Cole Multipass earned a badge
      Reacting Well
    • Reacting Well
      JLP earned a badge
      Reacting Well
  • Popular Contributors

    1. 1
      +primortal
      665
    2. 2
      ATLien_0
      283
    3. 3
      Michael Scrip
      224
    4. 4
      +FloatingFatMan
      190
    5. 5
      Steven P.
      146
  • Tell a friend

    Love Neowin? Tell a friend!