datetime and smalldatetime (T-SQL)

Date and time data types for representing date and time of day.

datetime
Date and time data from January 1, 1753, to December 31, 9999, to an accuracy of one three-hundredth second, or 3.33 milliseconds. Values are rounded to increments of .000, .003, or .007 milliseconds, as shown in the table.

 

Example Rounded example
01/01/98 23:59:59.999 1998-01-02 12:00:00.000
01/01/98 23:59:59.995,
01/01/98 23:59:59.996,
01/01/98 23:59:59.997, or
01/01/98 23:59:59.998
1998-01-01 23:59:59.997
01/01/98 23:59:59.992,
01/01/98 23:59:59.993,
01/01/98 23:59:59.994
1998-01-01 23:59:59.993
01/01/98 23:59:59.990 or
01/01/98 23:59:59.991
1998-01-01 23:59:59.990

SQL Server rejects all values it cannot recognize as dates between 1753 and 9999.

smalldatetime
Date and time data from January 1, 1900, through June 6, 2079, with accuracy to the minute.
Remarks

Values with the datetime data type are stored internally by Microsoft® SQL Server™ as two 4-byte integers. The first 4 bytes store the number of days before or after the base date, January 1, 1900. The base date is the system’s reference date. Values for datetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight.

The smalldatetime data type stores dates and times of day with less precision than datetime. SQL Server stores smalldatetime values as two 2-byte integers. The first 2 bytes store the number of days after January 1, 1900. The other 2 bytes store the number of minutes since midnight. Dates range from January 1, 1900, through June 6, 2079, with accuracy to the minute.

See Also
ALTER TABLE DECLARE @local_variable
CAST and CONVERT DELETE
CREATE TABLE INSERT
Data Type Conversion SET @local_variable
Data Types UPDATE

  


(c) 1988-98 Microsoft Corporation. All Rights Reserved.