The difference between date, DateTime, and smalldate three data types in SQL Server

Environment: SQL Server 2008 R2

Question: SQL Server date, datetime, Smalldate difference

Solve:

Datetime

Date and time data from January 1, 1753 to December 31, 9999, with an accuracy of 3% seconds (equal to 3.33 milliseconds or 0.00333 seconds). Adjust the value to the increment of. 000,. 003, or. 007 seconds, as shown in the following table.

The date and Time section, which can be represented by the date range from January 1, 1753 00:00:00.000 to December 31, 9999 23:59:59.997, is accurate to 3.33 milliseconds, and it requires 8 bytes of storage space.

smalldatetime

Date and time data from January 1, 1900 to June 6, 2079 are accurate to minutes. The smalldatetime value of 29.998 seconds or less is rounded down to the nearest minute, and the smalldatetime value of 29.999 seconds or higher is rounded up to the nearest minute. Requires 4 bytes of storage space.

Date

The newly introduced data type for SQL Server 2008. It represents a day, does not contain the time part, can represent the date range from the year of A.D. January 1 to the December 31, 9999. Requires only 3 bytes of storage space.

When reading this data, regardless of whether your field is datetime or smalldatetime read the same format (such as: 1900-01-01). DateTime does not display his milliseconds, but he will appear in milliseconds when comparing time, so that two time periods cannot be equal.