The main difference is range:
The range for SmallDateTime is January 1, 1900, through June 6, 2079
The range for DateTime is January 1, 1753, through December 31, 9999.
| Attribute | SmallDateTime | DateTime |
| Date Range | January 1, 1900, through June 6, 2079 | January 1, 1753, through December 31, 9999 |
| Time Range | 00:00:00 through 23:59:59 | 00:00:00 through 23:59:59.997 |
| Accuracy | 1 Minute | 3.33 Milli-seconds |
| Size | 4 Bytes | 8 Bytes |
| Default value | 1900-01-01 00:00:00 | 1900-01-01 00:00:00 |
The range for SmallDateTime is January 1, 1900, through June 6, 2079. A value outside of this range, is not allowed.
The following 2 queries have values outside of the range of SmallDateTime data type.
Insert into Employees ([SmallDateTime]) values (’01/01/1899′)
When executed, the above queries fail with the following error
The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value
The range for DateTime is January 1, 1753, through December 31, 9999. A value outside of this range, is not allowed.
The following query has a value outside of the range of DateTime data type.
Insert into Employees ([DateTime]) values (’01/01/1752′)
When executed, the above query fails with the following error
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
