Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : Similarities and Differences Between DATETIME and SMALLDATETIME Data Types
SQL Server Helper
Home > > Tip of the Day
SQL Server Helper - Tip of the Day

In SQL Server, each column, local variable, expression and parameter has a related data type.  A data type is an attribute that specifies the type of data that the object can hold, such as date and time data.  Two date/time-related data types have existed since SQL Server 2000, namely the DATETIME and SMALLDATETIME data types.

The following article shows the similarities and differences between the DATETIME data type and the SMALLDATETIME data type.

Similarities

  • Both data types define a date that is combined with a time of day that is based on a 24-hour clock.
  • Both data types are not daylight savings time aware and are not time zone offset aware.
  • Both data types have a default value of 1900-01-01 00:00:00
  • Both data types have been around since SQL Server 2000.

Differences

DATETIME

SMALLDATETIME

Date range is from 1753-01-01 through 9999-12-31

Date range is from 1900-01-01 through 2079-06-06

Time range is from 00:00:00 through 23:59:59.997

Time range is from 00:00:00 through 23:59:59

Storage size is 8 bytes

Storage size is 4 bytes

Accuracy is rounded to increments of .000, .003 and .007 seconds

Accuracy is to one minute

19 positions minimum to 23 maximum

Character length is 19 positions maximum

Back to Tip of the Day List Next Tip