Difference between DateTime and SmallDateTime in SQL Server

Suggested Videos
Part 124 - TRY_CONVERT function in SQL Server 2012
Part 125 - EOMONTH function in SQL Server 2012
Part 126 - DATEFROMPARTS function in SQL Server



In this video we will discuss the difference between DateTime and SmallDateTime in SQL Server



The following table summarizes the differences

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')

Insert into Employees ([SmallDateTime]) values ('07/06/2079')

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.