Date and Time Data Types

MSDN Documentation > SQL Server > Data Types > Date and Time Types

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

Common Date and Time Types

DATE

Stores a date value. It supports values from January 1, 1 AD to December 31, 9999 AD.

Storage Size: 3 bytes

-- Example declaration
DECLARE @EventDate DATE = '2023-10-27';

TIME

Stores a time of day value, independent of any date. It supports fractional seconds up to nanosecond precision.

Storage Size: 3 to 5 bytes (depending on precision)

-- Example declaration
DECLARE @MeetingTime TIME(7) = '14:30:00.1234567';

DATETIME

Stores both date and time information. It has a lower precision for fractional seconds compared to DATETIME2.

Storage Size: 8 bytes

Range: January 1, 1753, through December 31, 9999

-- Example declaration
DECLARE @LogTimestamp DATETIME = '2023-10-27 10:05:30';

SMALLDATETIME

Stores both date and time information, but with lower accuracy and a smaller range than DATETIME.

Storage Size: 4 bytes

Range: January 1, 1900, through June 6, 2079

Accuracy: Rounded to the nearest minute.

-- Example declaration
DECLARE @CreationTime SMALLDATETIME = '2023-10-27 10:05 AM';

DATETIME2

A more modern and precise date and time type. It offers a larger date range and higher precision for fractional seconds compared to DATETIME.

Storage Size: 6 to 8 bytes (depending on precision)

Range: January 1, 0001, through December 31, 9999

-- Example declaration
DECLARE @PreciseTimestamp DATETIME2(3) = '2023-10-27 10:05:30.500';

DATETIMEOFFSET

Stores date and time information, along with a time zone offset. This is useful when dealing with data from different time zones.

Storage Size: 8 to 10 bytes (depending on precision)

-- Example declaration
DECLARE @UtcTimestamp DATETIMEOFFSET = '2023-10-27 10:05:30.123 +00:00';

Type Comparison Table

Data Type Storage Size Date Range Time Precision Time Zone Support
DATE 3 bytes 0001-01-01 to 9999-12-31 N/A No
TIME 3-5 bytes N/A Up to 7 decimal places (nanoseconds) No
DATETIME 8 bytes 1753-01-01 to 9999-12-31 .000, .003, .007 seconds (approximate) No
SMALLDATETIME 4 bytes 1900-01-01 to 2079-06-06 Nearest minute No
DATETIME2 6-8 bytes 0001-01-01 to 9999-12-31 Up to 7 decimal places (nanoseconds) No
DATETIMEOFFSET 8-10 bytes 0001-01-01 to 9999-12-31 Up to 7 decimal places (nanoseconds) Yes (offset)

Recommendations

Recommendation: For new development, it is generally recommended to use DATE, TIME, DATETIME2, and DATETIMEOFFSET. These types offer better precision, a wider range, and more efficient storage compared to their older counterparts.

Tip: Use DATETIME2 when you need to store both date and time with high precision. Use DATETIMEOFFSET if your application needs to handle data from multiple time zones accurately.

Caution: Avoid using DATETIME and SMALLDATETIME in new applications unless there is a specific backward compatibility requirement. Their limitations in precision and range can lead to data integrity issues.

See Also