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