How to Choose the Best Date and Time Data Types for a SQL Server Database

Select the optimum data type for date and time columns to meet your requirements. It can minimize data storage needs, boost performance, and reduce expenses.

Brett Jordan

When designing and implementing a Microsoft SQL Server database, it is best to equip yourself with awareness of all available data types, including the date and time types. Choosing the best-suited date and time type for each column that will store date and time values will ensure that the database implementation will meet storage and processing requirements over the short and long terms.

This article describes each data and time data type and how to select the one that best meets your needs.

Date and Time Data Type Definitions

The following sections summarize the date and time data types available in SQL Server. See detailed descriptions in the Date & time type pages of Microsoft’s Transact-SQL (T-SQL) Reference documentation. All date and time data types, except for time, are based on the Gregorian calendar.

Here is an overview of the date and time data types:

  • date — Stores dates from 0001–01–01 through 9999–12–31.
  • datetime — Stores combined date and time values from 1753–01–01 00:00:00 through 9999–12–31 23:59:59.997 without reference to time zone.
  • datetime2 — Stores combined date and time values from 0001–01–01 00:00:00 through 9999–12–31 23:59:59.9999999 without reference to time zone.
  • datetimeoffset — Stores combined date and time values from 0001–01–01 00:00:00 through 9999–12–31 23:59:59.9999999 with reference to time zone.
  • smalldatetime — Stores combined date and time values from 1900–01–01 00:00:00 through 2079–06–06 23:59:59. It does not include fractions of a second.
  • time — Stores time values from 00:00:00.0000000 through 23:59:59.9999999.

If you need to create a new database or table, use the date, datetime2, datetimeoffset, and time data types. They align better with current SQL standards than do datetime and smalldatetime.

date

The date type defines a date value in SQL Server.

Format

The date data type is expressed as a 10-character value in the literal format YYYY-MM-DD.

Date Range

Valid date values range from 0001–01–01 through 9999–12–31.

Element Ranges

  • YYYY is a 4-digit year from 0001 through 9999.
  • MM is a 2-digit month from 01 through 12.
  • DD is a 2-digit day of the month from 01 through 31, depending on the month.

Storage

Values of the date type are stored in 3 bytes.

datetime

The datetime type defines a combined date and time value in SQL Server. It is based on a 24-hour clock and includes fractional seconds.

Format

The datetime data type is expressed as a 21 to 23-character value in the literal format YYYY-MM-DD hh:mm:ss.n*.

Date Range

Valid date values range from 1753–01–01 through 9999–12–31.

Time Range

Valid time values range from 00:00:00 through 23:59:59.997.

Element Ranges

  • YYYY is a 4-digit year from 1753 through 9999.
  • MM is a 2-digit month from 01 through 12.
  • DD is a 2-digit day of the month from 01 through 31, depending on the month.
  • hh is a 2-digit hour of the day from 01 through 24.
  • mm is a 2-digit minute of the hour from 00 through 59.
  • ss is a 2-digit second of the minute from 00 through 59.
  • n* is a 1, 2, or 3-digit fraction of a second from 0 through 999.

Precision

Fractions of a second in datetime values are rounded to increments of .000, .003, or .007.

Storage

Values of the datetime type are stored in 8 bytes.

datetime2

Like the datetime type, datetime2 defines a combined date and time value in SQL Server. It is based on a 24-hour clock and includes fractional seconds. However, it has a broader date range and a larger default fractional second precision. Also, the user can optionally specify a precision.

Format

The datetime data type is expressed as a 21 to 23-character value in the literal format YYYY-MM-DD hh:mm:ss[.n*].

Date Range

Valid date values range from 0001–01–01 through 9999–12–31.

Time Range

Valid time values range from 00:00:00 through 23:59:59.9999999.

Element Ranges

  • YYYY is a 4-digit year from 0001 through 9999.
  • MM is a 2-digit month from 01 through 12.
  • DD is a 2-digit day of the month from 01 through 31, depending on the month.
  • hh is a 2-digit hour of the day from 01 through 24.
  • mm is a 2-digit minute of the hour from 00 through 59.
  • ss is a 2-digit second of the minute from 00 through 59.
  • n* is a 0 to 7-digit fraction of a second from 0 through 999.

Precision

With a fractional second value of 0 to 7 digits, the datetimeoffset type is accurate to 100 nanoseconds.

Storage

Values of the datetime2 type are stored as 6, 7, or 8 bytes depending on their precision.

datetimeoffset

Like the datetime2 data type, the datetimeoffset type defines a combined date and time value in SQL Server and uses a 24-hour clock. However, it features time zone awareness.

Format

The datetimeoffset data type is expressed as a 21 to 23-character value in the literal format YYYY-MM-DD hh:mm:ss[.nnnnnnn] [{+|-}hh:mm].

Date Range

Valid date values range from 0001–01–01 through 9999–12–31.

Time Range

Valid time values range from 00:00:00 through 23:59:59.9999999.

Element Ranges

  • YYYY is a 4-digit year from 0001 through 9999.
  • MM is a 2-digit month from 01 through 12.
  • DD is a 2-digit day of the month from 01 through 31, depending on the month.
  • hh is a 2-digit hour of the day from 00 through 23.
  • mm is a 2-digit minute of the hour from 00 through 59.
  • ss is a 2-digit second of the minute from 00 through 59.
  • n* is a 0 to 7-digit fraction of a second from 0 through 9999999.
  • hh is a 2-digit hour that ranges from -14 to +14.
  • mm is a 2-digit minute from 00 to 59.

Precision

The datetimeoffset type is accurate to 100 nanoseconds.

Storage

Values of the datetimeoffset type are stored as 10 bytes.

smalldatetime

The smalldatetime type defines a combined date and time value in SQL Server. It uses a 24-hour clock. Unlike the datetime type, it does not include fractional seconds.

Format

The smalldatetime data type is expressed as a 19-character value in the literal format YYYY-MM-DD hh:mm:ss.

Date Range

Valid date values range from 1900–01–01 through 2079–06–06.

Time Range

Valid time values range from 00:00:00 through 23:59:59.

Element Ranges

  • YYYY is a 4-digit year from 1900 through 2079.
  • MM is a 2-digit month from 01 through 12.
  • DD is a 2-digit day of the month from 01 through 31, depending on the month.
  • hh is a 2-digit hour of the day from 01 through 24.
  • mm is a 2-digit minute of the hour from 00 through 59.
  • ss is a 2-digit second of the minute from 00 through 59.

Storage

Values of the smalldatetime type are stored in 4 bytes.

time

The time type defines a time of day value in SQL Server. It is based on a 24-hour clock and includes fractional seconds. It is accurate to 100 nanoseconds.

Format

The time data type is expressed as a 21 to 23-character value in the literal format hh:mm:ss.n*.

Time Range

Valid time values range from 00:00:00.0000000 through 23:59:59.9999999.

Element Ranges

  • hh is a 2-digit hour of the day from 01 through 24.
  • mm is a 2-digit minute of the hour from 00 through 59.
  • ss is a 2-digit second of the minute from 00 through 59.
  • n* is a 0 to 7-digit fraction of a second from 0 through 9999999.

Precision

See the precision table here for details.

Storage

Values of the time type are stored in 5 bytes.

Choosing Date and Time Data Types

When you create a physical database design or implement a database, it is vital to choose the best data type with the appropriate type, length, precision, and range for each column in each table. Doing so will ensure that the database will continue to meet the data storage and processing needs in the short and long terms. Selecting a type ill-matched to the requirement can result in negative ramifications, such as time-consuming and expensive rework of the database and code, the inability to store data of the required, size, range, or precision, or database and application downtime while needed changes are applied.

General Data Type Choice Principles

Selecting the best date and time data types to meet the requirements will result in these positive effects for each column of data:

  • It can store data to the desired precision. One cannot derive detailed data from summarized data. Likewise, one cannot obtain additional accuracy from a less-precise database column. For example, if you need a column that must store dates and times accurate to one-thousandth of a second, then create a column with the datetime2 or datetimeoffset type and specify a 3-digit fractional second.
  • It allows for the possible range of values. If you need to store dates and times with values before the year 1753, then the datetime type will not meet the needs. Instead, use datetime2 or datetimeoffset.
  • It does not waste space and might improve performance. While the cost of computer storage space is a fraction of what it was a decade ago, it is a finite resource. Conserving space can improve performance and limit interruptions and costs to implement more storage space when it runs low. It can help database backups and recoveries to run faster. It can also simplify the process of selecting and preparing data for use and view. For example, if a column needs to store only a date, then use a date type instead of a datetime2 or datetimeoffset type. By default, data selected from a column with the date type will be displayed in the format YYYY-MM-DD instead of YYYY-MM-DD hh:mm:ss.n* of the other types.
  • It will work well with other columns in the database. For example, the difference in hours between a column with a date type and another column with a datetime2 will return an imprecise value.

Data Types to Avoid

For new databases, avoid the datetime and smalldatetime data types. According to Microsoft, they do not comply well with current SQL standards.

Guidelines to Choose Date and Time Data Type

Follow the guidelines below to select the date and time data type to use to meet your needs.

Since Microsoft recommends only the date, datetime2, datetimeoffset, and time data types for new database implementations, this section does not cover the datetime and smalldatetime types.

date

Use the date type for a column with these needs:

  • Date values are needed
  • and time values are not required.

When the time does not need to be stored, storing dates in a 3-byte date column will conserve storage space, could improve performance, and may simplify data processing and display.

datetime2

Use the datetime2 type for columns with these needs:

  • Combined date and time values are needed
  • and time zone information is not required.

datetimeoffset

Use the datetimeoffset type for columns with these needs:

  • Combined date and time values are needed
  • and time zone information is required.

time

Use the time type for columns with these needs:

  • Time values are needed
  • and date values are not required.

Summary

I hope this article has provided you with a basic understanding of the SQL Server date and time data types and how to choose the best one to meet your needs. Specifying appropriate types ensures that the database will store values with the needed ranges and precisions. It can also save storage space, reduce costs, and ensure adequate performance.

About the Author

Randy Runtsch is a writer, data engineer, data analyst, programmer, photographer, cyclist, and adventurer. He and his wife live in southeastern Minnesota, U.S.A.

See another of Randy’s articles about SQL Server: Use Python and Bulk Insert to from CSV Files into SQL Server Tables.

Randy writes articles on public datasets to drive insights and decision-making, writing, programming, data engineering, data analytics, photography, wildlife, bicycle touring, and more.

Writer | Nature Photographer | Adventurer | Data Engineer | Data Analyst | Programmer

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store