Date and Time Data Types

This section details the date and time data types available in Transact-SQL (T-SQL).

Purpose: These data types are used to store date and time information, ranging from specific points in time to larger date ranges. They are crucial for applications requiring temporal tracking, scheduling, or historical analysis.

Date and Time Types Summary

Data Type Description Range Precision
date Stores a date value. 0001-01-01 through 9999-12-31 1 day
time Stores a time of day value. 00:00:00.0000000 through 23:59:59.9999999 100 nanoseconds
datetime Stores a date and time combination. 1753-01-01 00:00:00.000 through 9999-12-31 23:59:59.997 3.33 milliseconds
smalldatetime Stores a date and time combination. Less precision than datetime. 1900-01-01 00:00:00 through 2079-06-06 23:59:59 1 minute
datetime2 An extended version of datetime with a larger date range and higher precision. 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 100 nanoseconds
datetimeoffset A variation of datetime2 that includes a time zone offset. 0001-01-01 00:00:00.0000000 through 9999-12-31 23:59:59.9999999 100 nanoseconds
timestamp A database synonym for the rowversion data type. It is a binary number that guarantees uniqueness within a data set. N/A N/A
rowversion A data type that exposes underlying binary data that you can use to version-track SQL Server table rows. N/A N/A

date

Stores only the date. The format is YYYY-MM-DD.

Example: '2023-10-27'

time

Stores only the time of day. The format is hh:mm:ss.fraction. Fractional seconds are supported up to nanosecond precision.

Example: '14:30:05.1234567'

datetime

Stores a date and time value. It is a combination of the date and time types.

Example: '2023-10-27 14:30:05.123'

smalldatetime

Similar to datetime but with less precision and a more limited range. It stores time to the nearest minute.

Example: '2023-10-27 14:30:00'

datetime2

An improved version of datetime offering a wider date range and higher precision (up to 100 nanoseconds).

Example: '2023-10-27 14:30:05.123456789'

datetimeoffset

Stores date and time along with the time zone offset from UTC. This is useful for applications dealing with multiple time zones.

Example: '2023-10-27 14:30:05.1234567 +02:00'

timestamp and rowversion

These data types are not for storing dates or times. They are used to identify the time an update occurred to a row. timestamp is a synonym for rowversion.

Note: When you add a rowversion column to a table, SQL Server automatically provides a unique, incrementing binary number every time a row is updated.

Using Date and Time Functions

T-SQL provides numerous functions to manipulate date and time values:

  • GETDATE(): Returns the current database system timestamp as a datetime2(3) value.
  • SYSDATETIME(): Returns the current database system timestamp as a datetime2(7) value.
  • DATEPART(datepart, date): Returns an integer representing the specified part of a date.
  • DATEDIFF(datepart, startdate, enddate): Returns the difference between two dates in the specified datepart.
  • DATEADD(datepart, number, date): Returns a new date by adding an interval to a specified date.

SELECT
    GETDATE() AS CurrentDateTime,
    SYSDATETIME() AS CurrentSystemDateTime,
    DATEPART(year, '2023-10-27') AS YearPart,
    DATEDIFF(day, '2023-10-01', '2023-10-27') AS DaysDifference,
    DATEADD(month, 3, '2023-10-27') AS DateAfterThreeMonths;

Understanding and correctly using these data types and functions is essential for efficient data management and accurate temporal analysis in SQL Server.