Both SMALLDATETIME and DATETIME Data Types in Sql Server are used for storing Date and Time values in Sql Server. Below table summarizes some of the major difference between these two Data Types.
[ALSO READ] DateTime vs DateTime2
SMALLDATETIME |
DATETIME |
|
| FORMAT | YYYY-MM-DD hh:mm:ss | YYYY-MM-DD hh:mm:ss.nnn |
| MIN Value | 1900-01-01 00:00:00 | 1753-01-01 00:00:00 |
| MAX Value | 2079-06-06 23:59:00 | 9999-12-31 23:59:59.997 |
| Storage Size | 4 bytes | 8 bytes |
| Usage |  |
 |
| Accuracy | 1 Minute
Second’s values that are 29.998 seconds or less are rounded down to the nearest minute. And second’s values of 29.999 seconds or more are rounded up to the nearest minute. So seconds part value is always 00. Example 1: |
Rounded to increments of .000, .003, or .007 second It means: If time part in the date is 23:59:58.990 or 23:59:58.991, it will be stored as 23:59:58.990. Example If time part in the date is 23:59:58.992 or 23:59:58.993 or 23:59:58.994, it will rounded and stored as 23:59:58.993 Example 2: If time part in the date is 23:59:58.995 or 23:59:58.996 or 23:59:58.997 or 23:59:58.998, it will be rounded and stored as 23:59:58.997 If time part in the date is 23:59:58.999, it will be rounded and stored as 23:59:59.000 |
ALSO READ
- Varchar vs NVarchar
- Varchar vs Varchar(MAX)
- Char vs Varchar
- Text vs Varchar(Max)
- Union vs Union All
- DateTime vs DateTime2
- SET QUOTED_IDENTIFIER ON vs SET QUOTED_IDENTIFIER OFF
- Stored Procedure vs User Defined Function
- Primary Key vs Unique Key
- RAISERROR vs THROW
- Temporary Table vs Table Variable
- Len() vs Datalength()
- Sequence vs Identity
- DATEDIFF vs DATEDIFF_BIG
- LEFT JOIN vs LEFT OUTER JOIN
- RIGHT JOIN vs RIGHT OUTER JOIN
- JOIN vs INNER JOIN
- LEFT OUTER JOIN vs RIGHT OUTER JOIN
- SMALLDATETIME vs DATETIME