Difference between date and datetime

Brief explanation of types

Is not such a simple comparison.

The only common point between Date and DateTime is that the two are used to store dates.

The type DateTime and SmallDateTime are pre SQL Server 2008 types (they have been there since at least version 4.5 NT, the first Microsoft SQL Server) and many argue that they should be considered obsolete ( and they are only there for backward compatibility reasons).

DateTime always allocate 8 Bytes, the date horizon goes from 1753-01-01 to 9999-12-31 and the time with fixed precision of nanoseconds with increments of .000, .003, or .007 seconds, the why of this I will demonstrate at the end of this answer.

SmallDateTime it always allocates 4 Bytes, the date horizon goes from 1900-01-01 to 2079-06-06 (if you think this is far away, you are thinking the same as the programmers who conceived the date format with the two-digit year that created the year 2000 bug) and the time with fixed accuracy in seconds.

With the need to make SQL Server closer to SQL Standard, the types were includedDATE, TIME, DATETIME2 and DATETIMEOFFSET from SQL Server 2008 release.

Before (SQL Server 2008) you could only store dates including time, even if this was always zero. here fits a parallel : it is the same as storing Integers Using a Decimal type (float, double, etc..). You can, but you're wasting space doing it.

From SQL Server 2008, when there is a need to store only dates, without the time, the type DATE has been made available that always allocates 3 Bytes (practically a third of the type DATETIME) and its range goes from 1900-01-01 to 9999-12-31.

When you need to store only the time the type TIME has been created that allocates from 3 to 5 Bytes, depending on the accuracy chosen. Accuracy influences the amount of split-second digits. If 0 the time horizon goes from 00:00:00 to 23:59: 59. Se 7, 00: 00:00.0000000 to 23:59: 59.9999999.

The type DateTime2 which is the Union of the types Date and Time (and this statement is true even in the form in which the DateTime2 is stored internally), which allocates from 6 to 8 bytes depending on the accuracy of the fraction of seconds. A horizon from 1900-01-01 00:00:00.0000000 to 9999-12-31 23:59:59.9999999.

And to end the DATETIMEOFFSET which is type DateTime2 plus a time zone indicator. This was a limitation of Previous MSSQLServers, forcing the developer to use a varchar field, thereby losing access to all native functions for date handling, to store dates and time in ISO 8601 format : YYYY-MM-DDThh:mm: ss[.NNNNNNN] Z (UTC)

How types are stored

Internally, the type datetime is stored as two double words (DWORD). The first double word (most significant dword) is the number of days before or after (if a negative value is used) the base date (1900/01/01). The second double word (less significant DWORD) indicates the amount of clock pulses (ticks) after midnight. Each tick is 1/300 of a second.

If you convert a datetime into binary(8), you will see exactly how the information is stored:

Select convert(binary(8),cast('1900-01-00 00:00:00.000' as datetime))
0x0000000000000000

Select convert(binary(8),cast('1900-01-02 00:00:00.003' as datetime))
0x0000000100000001

We can clearly see that there are two DWORDs (4 bytes each):

00000001|00000001

The first word 0x00000001 that is intended for the date (number of days after 1900-01-01) and the second word 0x00000001 which determines the amount of ticks after midnight , 00: 00:00 (remembering that each tick 3.33333... thousandths of a second).

Now if you convert a date into binary(3) you can see that

Select convert(binary(3),cast('00010101' as date));
0x000000

Select convert(binary(3),cast('00010102' as date));
0x010000

Select convert(binary(3),cast('99991231' as date));
0xDAB937

Now if you convert a Time (zero-digit accuracy, for the sake of simplification) into binary(4) you can see that

Select convert(binary(4),cast('00:00:00' as time(0)));
0x00000000

Select convert(binary(4),cast('23:59:59' as time(0)));
0x007F5101

Note that both in the case of Date and Time given is stored from the smallest to the largest Byte, that is, the information is stored " lower end first ".

If you invert 0xda B9 37 you get 0X37 B9 DA, which in decimal represents 3652058, i.e. 9999-12-31 is exactly 3652058 days after 0001-01-01.

Same thing for Time, 0x7f 51 01 inverting 0x01 51 7F, which in decimal represents 86399 seconds after 00:00:00.

As I said earlier, DateTime2 is the Union of Date and Time, as we can see:

Select convert(binary(7),cast('0001-01-01 00:00:00' as datetime2(0)));
0x00000000000000

Select convert(binary(7),cast('9999-12-31 23:59:59' as datetime2(0)));
0x007F5101DAB937

Note that the first 3 bytes plus 0x007f5101dab937 is 0xdab937 which corresponds to the date and the remaining 0x007f5101 bytes correspond to the time.

Conclusion

It is not correct to compare the types DateTime with Date, in this case it would be more appropriate to compare DateTime with DateTime2.

According to various sources consulted, if you use MSSQL Server 2008+ use DateTime2 instead of DateTime.

Fonts

Inside the Storage Engine: Anatomy of a record

SQL Server 2008 Date and Time Data Types

How to Get SQL Server Dates and Times horrible Wrong

Datetime vs. Datetime2