List of directories in this document:
1. Base date in SQL Server
2
,
date range of the smalldatetime
3. The relationship between the date range of the smalldatetime and the unsigned 2-byte integer
4. Summary language
5. List of reference lists
base date in SQL Server
SQL Server provides a base date for datetime and smalldatetime date-time data types, which is 1900-01-01.
the following excerpt from MSDN:
The SQL Server 2005 Database engine stores the value of a datetime data type internally with two 4-byte integers. The first 4 bytes stores the number of days before or after the "base date" (that is, January 1, 1900). The base date is the system reference date. Another 4-byte storage day, expressed as a 1/300-second number after midnight.
The smalldatetime data type stores the date and time of day, but is less accurate than DateTime. The database engine stores the smalldatetime value as a two 2-byte integer. The first 2 bytes stores the number of days after January 1, 1900. Another 2 bytes stores the number of minutes elapsed after midnight.
Attention:
1, the above "base date" (that is, January 1, 1900) is the base date to be said in this article.
Date range of the smalldatetime
The date range of the smalldatetime is based on the relevant information in MSDN [1900-01-01, 2079-06-06].
smalldatetime relationship of date range and unsigned 2-byte integers
SQL Server assigns a value of 0 or 1900-01-01 to a variable or field column for datetime and smalldatetime data types, and the result is the same. The following T-SQL code works as follows: The maximum value for the smalldatetime date is 2079-06-06, if the assignment for that data is 65535
(this value is
unsigned 2-byte integer maximum value 2^16-1), the result is also for 2079-06-06, the following T-SQL code effect is as follows: from 0 corresponding to the 1900-01-01,65535 corresponding to 2079-06-06 corresponding relations, You can roughly get the date range of smalldatetime and the range of unsigned 2-byte integers exactly one by one corresponding. Can be proven through T-SQL, as follows:
Note:
1, the middle omitted part, only shows the data corresponding to the head and tail, the middle omitted.
2, function dbo.ufn_getnums can see the implementation of SQL Server Digital Auxiliary table.
Summary Language
The date range of the smalldatetime is corresponding to the range one by one of the unsigned 2-byte integer. For this correspondence, if the field in the data table is listed as a datetime data type, and the precision is days, you can save an integer and the date to save a corresponding relationship, you can refer to the base date 1900-01-01 as a starting point mapping, of course, you can choose another date, Also consider the range of the corresponding date-time data type.
List of reference lists
1.https://technet.microsoft.com/zh-cn/library/ms187819 (v=sql.90). aspx
2.Http://www.cnblogs.com/dzy863/p/5080832.html
Why is the date range for smalldatetime in SQL Server [1900-01-01, 2079-06-06]
