Difference between date and datetime

Question

I was researching the type of data SQL SERVER and I came across this table

What would be the real difference between date and datetime data? Time, minute, second notation only ?

What would that column be Precision?

Answer 1

The difference is not only the notation is the content, it is the value that it stores. The first has no time, only the date, the second has both, this is important because it is possible to extract only the date or only the time of the second, but the first has only date.

The precision is precisely how granular it can be. We can think about how many data houses he owns, or what the lowest value he can give you. The lowest value of date is a single day, not to go beyond this. In the datetime goes up to a value of 1 second divided by 300, you cannot catch 1 thousandth of a second, for example. This information is not present in the column.

Obviously if you have more information, it takes up more space, as shown in the table.

Answer 2

Brief explanation about the types

It’s not that simple a comparison.

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

The guy DateTime and SmallDateTime are pre-sql server types 2008 (they are there since, at least, version 4.5NT, the first Microsoft SQL Server) and many argue that they should be considered Obsolete ( and they are only there for backwards compatibility reasons).

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

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

With the need to bring SQL Server closer to SQL Standard, types were included DATE, TIME, DATETIME2 and DATETIMEOFFSET from SQL Server 2008 version.

Before (SQL Server 2008) you could only store dates including time, even if it was always zero. Here fits a parallel: It’s the same as storing Integers using a Decimal type (float, double, etc.). You can, but you’re wasting space doing so.

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

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

The guy DateTime2 which is the union of types Date and Time (and that statement is true even in the form in which the DateTime2 is stored internally), which allocates 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 finalize the DATETIMEOFFSET who is the type DateTime2 plus a time zone indicator. This was a limitation of the previous Mssqlservers, forcing the developer to use a varchar field, thereby losing access to all native date handling functions, 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 used a negative value) of the base date (1900/01/01). The second double word (DWORD less significant) indicates the amount of clock pulses (ticks) after midnight. Each tick is 1/300 of a second.

If you convert a datetime in 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 to date (number of days after 1900-01-01) and the second word 0x00000001 that determines the amount of ticks after midnight , 00:00:00 (remembering that each tick 3.33333... thousandths of a second).

Now if you convert one date in 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 one Time (zero digit accuracy for reasons of simplification) in 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 how much in the Time data is stored from the smallest to the largest Byte, ie, the information is stored "lower end first".

If Voce invert 0xDA B9 37 you get 0x37 B9 DA, which in decimal represents 3652058, ie 9999-12-31 is exactly 3652058 days after 0001-01-01.

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

Like I said before, 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 most significant bytes of 0x007F5101DAB937 is 0xDAB937 which corresponds to Date and that the remaining bytes 0x007F5101 correspond to Time.

Completion

It’s not right to compare 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.

Sources

Inside the Storage Engine: Anatomy of a record

SQL Server 2008 Date and Time Data Types

How to Get SQL Server Dates and Times Horribly Wrong

Datetime vs. Datetime2