HI WELCOME TO SIRING

Difference between DateTime and SmallDateTime in SQL Server

Leave a Comment

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


The following table summarizes the differences

AttributeSmallDateTimeDateTime
Date RangeJanuary 1, 1900, through June 6, 2079January 1, 1753, through December 31, 9999
Time Range00:00:00 through 23:59:5900:00:00 through 23:59:59.997
Accuracy1 Minute3.33 Milli-seconds
Size4 Bytes8 Bytes
Default value1900-01-01 00:00:001900-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.

0 comments:

Post a Comment

Note: only a member of this blog may post a comment.