Tip of the Day : Similarities and Differences Between DATETIME and SMALLDATETIME Data Types

Welcome to SQL Server Helper !!!

This site is intended for those who are beginning to use SQL Server as part of their day-to-day activities.  You will find in this site a collection of useful functions, triggers, stored procedures and tips and tricks related to SQL Server.

Should you have any comments or questions regarding this site or if you want to ask SQL Server-related questions, e-mail us here.

We hope we are able to help and we are glad to help!!!

SQL Server Tip of the Day - October 28, 2021

Similarities and Differences Between DATETIME and SMALLDATETIME Data Types

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

SQL Server 2012

SQL Server 2008

User-Defined Functions

Date Functions

A collection of useful user-defined functions that deal with dates.

String Functions

A collection of useful user-defined functions that deal with strings (varchar/char/nvarchar/nchar).

Tree Functions

A collection of useful user-defined functions that deal with tree or hierarchical design structures.

Table-Valued Functions

A collection of useful table-valued user-defined functions that can be used to join with other tables.

SQL Server Built-in Functions

A reference to all built-in functions available within SQL Server grouped into categories.

Tips and Tricks

A collection of useful SQL Server-related tips and tricks:

SQL Server Error Messages

A list of SQL Server error messages and for certain error messages, discusses ways on how to solve the error or work around them:

Frequently Asked Questions