SQL Server Date and Time Data Types

Introduction

SQL Server provides a rich set of data types for storing and manipulating date and time information. Choosing the right data type is crucial for data integrity, performance, and storage efficiency. This documentation covers the primary date and time data types available in SQL Server.

datetime2

The datetime2 data type is the recommended data type for storing date and time values in newer versions of SQL Server. It offers a wider range of dates and higher precision than the older datetime type.

• Range: January 1, 0001 to December 31, 9999
• Precision: Up to 100 nanoseconds. The default precision is 7 digits.
• Storage: 6 to 8 bytes, depending on precision.

CREATE TABLE Events (
    EventID INT PRIMARY KEY,
    EventName VARCHAR(100),
    EventDateTime DATETIME2(3) -- Stores date and time with millisecond precision
);

Precision

You can specify the precision for datetime2 when declaring the column:

• DATETIME2(0): Seconds
• DATETIME2(1): Tenths of a second
• DATETIME2(2): Hundredths of a second
• DATETIME2(3): Milliseconds
• DATETIME2(4): Ten-thousandths of a second
• DATETIME2(5): Hundred-thousandths of a second
• DATETIME2(6): Microseconds
• DATETIME2(7): Nanoseconds

smalldatetime

smalldatetime is an older data type that stores date and time values with less precision and a smaller range compared to datetime and datetime2.

• Range: January 1, 1900 to June 6, 2079
• Precision: Accurate to the minute.
• Storage: 4 bytes.

Note: While it uses less storage, its limited range and precision make it less suitable for most modern applications.

datetime

The datetime data type is a widely used older data type for storing date and time values. It has a broader range than smalldatetime but less precision than datetime2.

• Range: January 1, 1753 to December 31, 9999
• Precision: Accurate to .003 seconds (approximately 3.33 milliseconds).
• Storage: 8 bytes.

INSERT INTO Events (EventID, EventName, EventDateTime)
VALUES (1, 'System Start', '2023-10-27 10:30:00.123');

datetimeoffset

datetimeoffset stores date and time data along with time zone information. This is particularly useful when working with data from multiple time zones.

• Range: Same as datetime2 (January 1, 0001 to December 31, 9999)
• Precision: Same as datetime2 (up to 100 nanoseconds).
• Storage: 10 bytes.
• Includes: Date, time, and time zone offset (e.g., +05:00).

INSERT INTO Events (EventID, EventName, EventDateTime)
VALUES (2, 'Global Meeting', '2023-10-27 15:00:00.000 +01:00');

date

The date data type stores only the date portion of a date and time value.

• Range: January 1, 0001 to December 31, 9999
• Storage: 3 bytes.

CREATE TABLE Birthdays (
    PersonID INT PRIMARY KEY,
    Name VARCHAR(100),
    BirthDate DATE
);

time

The time data type stores only the time portion of a date and time value.

• Range: 00:00:00.0000000 to 23:59:59.9999999
• Precision: Up to 100 nanoseconds. The default precision is 7 digits.
• Storage: 3 to 5 bytes, depending on precision.

CREATE TABLE DailySchedule (
    ScheduleID INT PRIMARY KEY,
    Activity VARCHAR(100),
    StartTime TIME(0) -- Stores time without fractional seconds
);

Overview Table

Here's a summary of the key date and time data types:

Data Type	Range	Precision	Storage	Time Zone
date	0001-01-01 to 9999-12-31	Day	3 bytes	No
time	00:00:00.0000000 to 23:59:59.9999999	100 nanoseconds (default 7)	3-5 bytes	No
smalldatetime	1900-01-01 to 2079-06-06	Minute	4 bytes	No
datetime	1753-01-01 to 9999-12-31	0.003 seconds	8 bytes	No
datetime2	0001-01-01 to 9999-12-31	100 nanoseconds (default 7)	6-8 bytes	No
datetimeoffset	0001-01-01 to 9999-12-31	100 nanoseconds (default 7)	10 bytes	Yes

Comparison and Considerations

When choosing a data type, consider the following:

• Precision Required: If you need sub-second precision, datetime2 or time are suitable.
• Range of Dates: smalldatetime has a limited range. If your data extends beyond 1900 or 2079, use datetime or datetime2.
• Time Zone Support: If your application handles data from multiple time zones, datetimeoffset is the best choice.
• Storage Efficiency: For simple dates without time, date is the most efficient. For times without dates, use time.
• Compatibility: If you need to maintain compatibility with older applications or systems that use datetime, you might continue using it, but for new development, datetime2 is generally preferred.

Recommendations

• For new development requiring date and time storage, use datetime2 unless specific reasons dictate otherwise.
• Use date for storing only dates.
• Use time for storing only times.
• Use datetimeoffset when time zone awareness is critical.
• Avoid smalldatetime for new applications due to its limitations.