Anatomy of SQLSERVER Article 4 OrcaMDF's analysis of dates data)

Anatomy of SQLSERVER Article 4 Analysis of dates-type data in OrcaMDF improve. dkparsing-dates-in-orcamdf has several different date-related types in SQLSERVER. Currently, OrcaMDF supports three most common date types: date, datetime, and smalldatetimeSqlDate.

Anatomy of the fourth article of SQL Server OrcaMDF on dates type data parsing (translation) http://improve.dk/parsing-dates-in-orcamdf/ in SQL Server there are several different date-related types, currently OrcaMDF supports three most commonly used date types: date, datetime and smalldatetime SqlDate are implemented in three types of date

Anatomy of SQLSERVER Article 4 OrcaMDF's analysis of dates data)

Http://improve.dk/parsing-dates-in-orcamdf/

There are several date-related types in SQLSERVER. Currently, OrcaMDF supports three most common date types: date, datetime, and smalldatetime.

SqlDate implementation

The date type is the simplest among the three types. It is a three-byte fixed-length type that stores the date value. It supports the date range from 0001-01-01 to 9999-12-31.

The default value is 1900-01-01.

What's more, there is no standard implementation in. NET that supports three-byte integer types, only the short type and int type, but they are not too big or too small.

In addition, to read the date value correctly, we must convert the 4-byte integer of. NET to obtain the correct number.

Once we get the value of date, we can create a default datetime type and add the number of days to it.

public class SqlDate : ISqlType{    public bool IsVariableLength    {        get { return false; }    }    public short? FixedLength    {        get { return 3; }    }    public object GetValue(byte[] value)    {        if (value.Length != 3)            throw new ArgumentException("Invalid value length: " + value.Length);        // Magic needed to read a 3 byte integer into .NET's 4 byte representation.        // Reading backwards due to assumed little endianness.        int date = (value[2] << 16) + (value[1] << 8) + value[0];        return new DateTime(1, 1, 1).AddDays(date);    }}

Related tests

using System;using NUnit.Framework;using OrcaMDF.Core.Engine.SqlTypes;namespace OrcaMDF.Core.Tests.Engine.SqlTypes{    [TestFixture]public class SqlDateTests{        [Test]public void GetValue(){var type = new SqlDate();var input = new byte[] { 0xf6, 0x4c, 0x0b };Assert.AreEqual(new DateTime(2028, 09, 09), Convert.ToDateTime(type.GetValue(input)));input = new byte[] { 0x71, 0x5c, 0x0b };Assert.AreEqual(new DateTime(2039, 07, 17), Convert.ToDateTime(type.GetValue(input)));}        [Test]public void Length(){var type = new SqlDate();Assert.Throws(() => type.GetValue(new byte[2]));Assert.Throws(() => type.GetValue(new byte[4]));}}}

SqlDateTime implementation

The date type can only store dates, while the datetime type can store both date and time.

Datetime stores 8-byte fixed-length data values. The first part is time (4 bytes), and the second part is date (4 bytes)

The date Calculation Section is basically the same as the date type described above, but this time the date section is a four-byte integer, which is easier to process than the preceding example. The date type above is three bytes.

The time part is stored as the number of ticks from midnight, and a tick is 1/Seconds. to display the tick value, we first define a constant, and the constant value is 10 days/3d.

Each part of time is actually stored in the same integer value (such as time, minute, second, millisecond). Therefore, to independently access these individual parts, we must

Perform some conversions (including modulo and Division)

Partial computing hours X/300/60/60 Minutes X/300/60% 60 seconds X/300% 60 milliseconds X % 300 * 10d/3d

public class SqlDateTime : ISqlType{    private const double CLOCK_TICK_MS = 10d/3d;    public bool IsVariableLength    {        get { return false; }    }    public short? FixedLength    {        get { return 8; }    }    public object GetValue(byte[] value)    {        if (value.Length != 8)            throw new ArgumentException("Invalid value length: " + value.Length);        int time = BitConverter.ToInt32(value, 0);        int date = BitConverter.ToInt32(value, 4);        return new DateTime(1900, 1, 1, time/300/60/60, time/300/60%60, time/300%60, (int)Math.Round(time%300*CLOCK_TICK_MS)).AddDays(date);    }}

Related tests

using System;using NUnit.Framework;using OrcaMDF.Core.Engine.SqlTypes;namespace OrcaMDF.Core.Tests.Engine.SqlTypes{    [TestFixture]public class SqlDateTimeTests{        [Test]public void GetValue(){var type = new SqlDateTime();byte[] input;input = new byte[] { 0x5e, 0x3b, 0x5d, 0x00, 0x25, 0x91, 0x00, 0x00 };Assert.AreEqual(new DateTime(2001, 09, 25, 05, 39, 26, 820), (DateTime)type.GetValue(input));input = new byte[] { 0xb6, 0x87, 0xf0, 0x00, 0xd1, 0x8b, 0x00, 0x00 };Assert.AreEqual(new DateTime(1997, 12, 31, 14, 35, 44, 607), (DateTime)type.GetValue(input));input = new byte[] { 0x2d, 0xfd, 0x1c, 0x01, 0x4a, 0x75, 0x00, 0x00 };Assert.AreEqual(new DateTime(1982, 03, 18, 17, 17, 36, 790), (DateTime)type.GetValue(input));input = new byte[] { 0xff, 0x81, 0x8b, 0x01, 0x7f, 0x24, 0x2d, 0x00 };Assert.AreEqual(new DateTime(9999, 12, 31, 23, 59, 59, 997), (DateTime)type.GetValue(input));}        [Test]public void Length(){var type = new SqlDateTime();Assert.Throws(() => type.GetValue(new byte[9]));Assert.Throws(() => type.GetValue(new byte[7]));}}}

SqlSmallDateTime implementation

Smalldatetime is a good data type when you need to store the date value in the range value (1900 ~ 2079) and precise to seconds

In most cases, the accuracy to the second is enough, saving a lot of space at a time interval within a range and when the exact value is not required to be too precise.

The smalldatetime data type occupies only four bytes. The first two bytes are stored in minutes from midnight, and the last two bytes are stored in the date. The default value is-1-1.

The processing method is similar to datetime, but uses a smaller range.

Partial computing hours X/60 Minutes X % 60

public class SqlSmallDateTime : ISqlType{    public bool IsVariableLength    {        get { return false; }    }    public short? FixedLength    {        get { return 4; }    }    public object GetValue(byte[] value)    {        if (value.Length != 4)            throw new ArgumentException("Invalid value length: " + value.Length);        ushort time = BitConverter.ToUInt16(value, 0);        ushort date = BitConverter.ToUInt16(value, 2);        return new DateTime(1900, 1, 1, time / 60, time % 60, 0).AddDays(date);    }}

Related tests

using System;using NUnit.Framework;using OrcaMDF.Core.Engine.SqlTypes;namespace OrcaMDF.Core.Tests.Engine.SqlTypes{    [TestFixture]public class SqlSmallDateTimeTests{        [Test]public void GetValue(){var type = new SqlSmallDateTime();var input = new byte[] { 0xab, 0x02, 0x5d, 0x26 };Assert.AreEqual(new DateTime(1926, 11, 22, 11, 23, 0), Convert.ToDateTime(type.GetValue(input)));input = new byte[] { 0x49, 0x03, 0x99, 0x09 };Assert.AreEqual(new DateTime(1906, 9, 24, 14, 1, 0), Convert.ToDateTime(type.GetValue(input)));}        [Test]public void Length(){var type = new SqlSmallDateTime();Assert.Throws(() => type.GetValue(new byte[3]));Assert.Throws(() => type.GetValue(new byte[5]));}}}

Article 4