Why is the date range for smalldatetime in SQL Server [1900-01-01, 2079-06-06]

Source: Internet
Author: User

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.


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]

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.