Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

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.

1 comment:

  1. Hi sir
    ur toutorial are Fantastics
    can u upload WebAPI Videos

    ReplyDelete

If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.