Ask Learn
Preview
Ask Learn is an AI assistant that can answer questions, clarify concepts, and define terms using trusted Microsoft documentation.
Please sign in to use Ask Learn.
Sign inNote
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Are data types that are used for representing the date and the time of day.
datetime and smalldatetime
Represent the date and the time of day.Data type Range Accuracy datetime
January 1, 1753, through December 31, 9999
3.33 milliseconds
smalldatetime
January 1, 1900, through June 6, 2079
1 minute
Values with the datetime data type are stored internally by the SQL Server 2005 Database Engine 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 reference date. The other 4 bytes store the time of day represented as the number of 1/300-second units after midnight.
The smalldatetime data type stores dates and times of day with less precision than datetime. The Database Engine 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.
datetime values are rounded to increments of .000, .003, or .007 seconds, as shown in the following table.
| Example | Rounded example |
|---|---|
01/01/98 23:59:59.999 |
1998-01-02 00: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, or 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 |
smalldatetime values that are 29.998 seconds or less are rounded down to the nearest minute; values of 29.999 seconds or more are rounded up to the nearest minute.
--Returns time as 12:35.
SELECT CAST('2003-05-08 12:35:29.998' AS smalldatetime);
GO
--Returns time as 12:36.
SELECT CAST('2003-05-08 12:35:29.999' AS smalldatetime);
GO
Date and Time Functions (Transact-SQL)
Data Types (Transact-SQL)
ALTER TABLE (Transact-SQL)
CAST and CONVERT (Transact-SQL)
CREATE TABLE (Transact-SQL)
DECLARE @local\_variable (Transact-SQL)
DELETE (Transact-SQL)
INSERT (Transact-SQL)
SET @local\_variable (Transact-SQL)
UPDATE (Transact-SQL)
Data Type Conversion (Database Engine)
ISO 8601 Format
Alphabetic Date Format
Numeric Date Format
ODBC Datetime Format
Time Formats
Unseparated String Format